/*
 * @(#)WorkflowStep.java
 *
 * Copyright (c) 2003 DCIVision Ltd
 * All rights reserved.
 *
 * This software is the confidential and proprietary information of DCIVision
 * Ltd ("Confidential Information").  You shall not disclose such Confidential
 * Information and shall use it only in accordance with the terms of the license
 * agreement you entered into with DCIVision Ltd.
 */
package com.dcivision.workflow.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;
import java.util.Vector;

import com.dcivision.framework.ApplicationException;
import com.dcivision.framework.ErrorConstant;
import com.dcivision.framework.GlobalConstant;
import com.dcivision.framework.RecordNotFoundException;
import com.dcivision.framework.SessionContainer;
import com.dcivision.framework.TextUtility;
import com.dcivision.framework.UserInfoFactory;
import com.dcivision.framework.Utility;
import com.dcivision.framework.bean.AbstractBaseObject;
import com.dcivision.framework.dao.AbstractDAObject;
import com.dcivision.framework.dao.AbstractQuery;
import com.dcivision.framework.dao.SubQuery;
import com.dcivision.framework.web.AbstractSearchForm;
import com.dcivision.staff.bean.StaffRecord;
import com.dcivision.staff.dao.StaffRecordDAObject;
import com.dcivision.user.bean.MtmUserGroupUserRole;
import com.dcivision.user.bean.MtmUserRecordUserGroup;
import com.dcivision.user.bean.MtmUserRecordUserRole;
import com.dcivision.user.bean.UserDelegation;
import com.dcivision.user.bean.UserGroup;
import com.dcivision.user.bean.UserRole;
import com.dcivision.user.dao.MtmUserGroupUserRoleDAObject;
import com.dcivision.user.dao.MtmUserRecordUserGroupDAObject;
import com.dcivision.user.dao.MtmUserRecordUserRoleDAObject;
import com.dcivision.user.dao.UserDelegationDAObject;
import com.dcivision.user.dao.UserGroupDAObject;
import com.dcivision.user.dao.UserRoleDAObject;
import com.dcivision.workflow.bean.MtmWorkflowStepUserActor;
import com.dcivision.workflow.bean.WorkflowAction;
import com.dcivision.workflow.bean.WorkflowProgress;
import com.dcivision.workflow.bean.WorkflowStep;
import com.dcivision.workflow.bean.WorkflowStepFormEquat;
import com.dcivision.workflow.bean.WorkflowStepRoute;
import com.dcivision.workflow.core.AutomaticTaskFactory;
import com.dcivision.workflow.core.SystemWorkflowConstant;
import com.dcivision.workflow.core.WorkflowProcessor;

/**
 * WorkflowStepDAObject.java
 *
 * This class is the data access bean for table "WORKFLOW_STEP".
 *
 * @author Angus Shiu
 * @company DCIVision Limited
 * @creation date 05/08/2003
 * @version $Revision: 1.57.2.2 $
 */

public class WorkflowStepDAObject extends AbstractDAObject {
  
  public static final String REVISION = "$Revision: 1.57.2.2 $";
  
  public static final String TABLE_NAME = "WORKFLOW_STEP";
  
  public WorkflowStepDAObject(SessionContainer sessionContainer, Connection dbConn) {
    super(sessionContainer, dbConn);
  }
  
  protected void initDBSetting() {
    this.baseTableName = TABLE_NAME;
    this.vecDBColumn.add("ID");
    this.vecDBColumn.add("WORKFLOW_RECORD_ID");
    this.vecDBColumn.add("STEP_SEQ");
    this.vecDBColumn.add("STEP_NAME");
    this.vecDBColumn.add("ACTION_TYPE");
    this.vecDBColumn.add("ACTION_ID");
    this.vecDBColumn.add("DESCRIPTION");
    this.vecDBColumn.add("WORKFLOW_RULE");
    this.vecDBColumn.add("FORM_SECTION_ID");
    this.vecDBColumn.add("PRIORITY");
    this.vecDBColumn.add("ALLOW_ASSIGN_DUE_DATE");
    this.vecDBColumn.add("ALLOW_ASSIGN_PRIORITY");
    this.vecDBColumn.add("ON_COMPLETE_ACK_METHOD");
    this.vecDBColumn.add("ON_NO_RESPONSE_FOR");
    this.vecDBColumn.add("WORKING_CALENDAR_ID");    
    this.vecDBColumn.add("ON_NO_RESPONSE_AFTER");
    this.vecDBColumn.add("ON_NO_RESPONSE_ACTION");
    this.vecDBColumn.add("ON_NO_RESPONSE_NEXT_STEP_ID");
    this.vecDBColumn.add("ON_NO_RESPONSE_ACK_METHOD");
    this.vecDBColumn.add("FILTER_BY");
    this.vecDBColumn.add("ALLOW_STOP");
    this.vecDBColumn.add("RECORD_STATUS");
    this.vecDBColumn.add("UPDATE_COUNT");
    this.vecDBColumn.add("CREATOR_ID");
    this.vecDBColumn.add("CREATE_DATE");
    this.vecDBColumn.add("UPDATER_ID");
    this.vecDBColumn.add("UPDATE_DATE");
  }
  public synchronized AbstractBaseObject getStepByID(Integer id) throws ApplicationException{
    return this.getByID(id);
  }
  protected synchronized AbstractBaseObject getByID(Integer id) throws ApplicationException {
    PreparedStatement preStat = null;
    ResultSet rs = null;
    StringBuffer sqlStat = new StringBuffer();   
    synchronized(dbConn) {
      try {
        sqlStat.append("SELECT A.ID, A.WORKFLOW_RECORD_ID, A.STEP_SEQ, A.STEP_NAME, A.ACTION_TYPE, A.ACTION_ID, A.DESCRIPTION, A.WORKFLOW_RULE, A.FORM_SECTION_ID, A.PRIORITY, A.ALLOW_ASSIGN_DUE_DATE, A.ALLOW_ASSIGN_PRIORITY, A.ALLOW_STOP, A.ON_COMPLETE_ACK_METHOD, A.FILTER_BY, A.ON_NO_RESPONSE_FOR, A.ON_NO_RESPONSE_CALENDAR_ID, A.ON_NO_RESPONSE_AFTER, A.ON_NO_RESPONSE_ACTION, A.ON_NO_RESPONSE_NEXT_STEP_ID, A.ON_NO_RESPONSE_ACK_METHOD, A.RECORD_STATUS, A.UPDATE_COUNT, A.CREATOR_ID, A.CREATE_DATE, A.UPDATER_ID, A.UPDATE_DATE ");
        sqlStat.append("FROM   WORKFLOW_STEP A ");
        sqlStat.append("WHERE  A.ID = ? AND A.RECORD_STATUS = ? ");
        preStat = dbConn.prepareStatement(sqlStat.toString(), ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
        this.setPrepareStatement(preStat, 1, id);
        this.setPrepareStatement(preStat, 2, GlobalConstant.RECORD_STATUS_ACTIVE);
        log.debug("sqlStat="+sqlStat);//simon_add
        rs = preStat.executeQuery();
        if (rs.next()) {
          WorkflowStep tmpWorkflowStep = new WorkflowStep();
          tmpWorkflowStep.setID(getResultSetInteger(rs, "ID"));
          tmpWorkflowStep.setWorkflowRecordID(getResultSetInteger(rs, "WORKFLOW_RECORD_ID"));
          tmpWorkflowStep.setStepSeq(getResultSetInteger(rs, "STEP_SEQ"));
          tmpWorkflowStep.setStepName(getResultSetString(rs, "STEP_NAME"));
          tmpWorkflowStep.setActionType(getResultSetString(rs, "ACTION_TYPE"));
          tmpWorkflowStep.setActionID(getResultSetInteger(rs, "ACTION_ID"));
          tmpWorkflowStep.setDescription(getResultSetString(rs, "DESCRIPTION"));
          tmpWorkflowStep.setWorkflowRule(getResultSetString(rs, "WORKFLOW_RULE"));
          tmpWorkflowStep.setSectionID(getResultSetString(rs, "FORM_SECTION_ID"));
          tmpWorkflowStep.setPriority(getResultSetString(rs, "PRIORITY"));
          tmpWorkflowStep.setAllowAssignDueDate(getResultSetString(rs, "ALLOW_ASSIGN_DUE_DATE"));
          tmpWorkflowStep.setAllowAssignPriority(getResultSetString(rs, "ALLOW_ASSIGN_PRIORITY"));
          tmpWorkflowStep.setAllowStop(getResultSetString(rs, "ALLOW_STOP"));
          tmpWorkflowStep.setOnCompleteAckMethod(getResultSetString(rs, "ON_COMPLETE_ACK_METHOD"));
          tmpWorkflowStep.setOnNoResponseFor(getResultSetInteger(rs, "ON_NO_RESPONSE_FOR"));
          tmpWorkflowStep.setOnNoResponseCalendarID(getResultSetString(rs, "ON_NO_RESPONSE_CALENDAR_ID"));
          tmpWorkflowStep.setOnNoResponseAfter(getResultSetTimestamp(rs, "ON_NO_RESPONSE_AFTER"));
          tmpWorkflowStep.setOnNoResponseAction(getResultSetString(rs, "ON_NO_RESPONSE_ACTION"));
          tmpWorkflowStep.setOnNoResponseNextStepID(getResultSetInteger(rs, "ON_NO_RESPONSE_NEXT_STEP_ID"));
          tmpWorkflowStep.setOnNoResponseAckMethod(getResultSetString(rs, "ON_NO_RESPONSE_ACK_METHOD"));
          tmpWorkflowStep.setFilterBy(getResultSetInteger(rs, "FILTER_BY"));
          tmpWorkflowStep.setRecordStatus(getResultSetString(rs, "RECORD_STATUS"));
          tmpWorkflowStep.setUpdateCount(getResultSetInteger(rs, "UPDATE_COUNT"));
          tmpWorkflowStep.setCreatorID(getResultSetInteger(rs, "CREATOR_ID"));
          tmpWorkflowStep.setCreateDate(getResultSetTimestamp(rs, "CREATE_DATE"));
          tmpWorkflowStep.setUpdaterID(getResultSetInteger(rs, "UPDATER_ID"));
          tmpWorkflowStep.setUpdateDate(getResultSetTimestamp(rs, "UPDATE_DATE"));
          tmpWorkflowStep.setCreatorName(UserInfoFactory.getUserFullName(tmpWorkflowStep.getCreatorID()));
          tmpWorkflowStep.setUpdaterName(UserInfoFactory.getUserFullName(tmpWorkflowStep.getUpdaterID()));         
          return(tmpWorkflowStep);
        } else {
          throw new RecordNotFoundException();
        }
      } catch (ApplicationException appEx) {
        throw appEx;
      } catch (SQLException sqle) {
        log.error(sqle, sqle);
        throw new ApplicationException(ErrorConstant.DB_GENERAL_ERROR, sqle, sqle.toString());
      } catch (Exception e) {
        log.error(e, e);
        throw new ApplicationException(ErrorConstant.DB_SELECT_ERROR, e);
      } finally {
        try { rs.close(); } catch (Exception ignore) {} finally { rs = null; }
        try { preStat.close(); } catch (Exception ignore) {} finally { preStat = null; }
      }
    }
  }
  
  protected synchronized List getList(AbstractSearchForm searchForm) throws ApplicationException {
    PreparedStatement preStat = null;
    PreparedStatement preStatCnt = null;
    ResultSet rs = null;
    ResultSet rsCnt = null;
    StringBuffer sqlStat = new StringBuffer();
    StringBuffer sqlStatCnt = new StringBuffer();
    List result = new ArrayList();
    int totalNumOfRecord = 0;
    int rowLoopCnt = 0;
    int startOffset = TextUtility.parseInteger(searchForm.getCurStartRowNo());
    int pageSize = TextUtility.parseInteger(searchForm.getPageOffset());
    
    synchronized(dbConn) {
      try {
        sqlStat.append("SELECT A.ID, A.WORKFLOW_RECORD_ID, A.STEP_SEQ, A.STEP_NAME, A.ACTION_TYPE, A.ACTION_ID, A.DESCRIPTION, A.WORKFLOW_RULE, A.FORM_SECTION_ID, A.PRIORITY, A.ALLOW_ASSIGN_DUE_DATE, A.ALLOW_ASSIGN_PRIORITY, A.ALLOW_STOP, A.ON_COMPLETE_ACK_METHOD, A.FILTER_BY, A.ON_NO_RESPONSE_FOR, A.ON_NO_RESPONSE_CALENDAR_ID, A.ON_NO_RESPONSE_AFTER, A.ON_NO_RESPONSE_ACTION, A.ON_NO_RESPONSE_NEXT_STEP_ID, A.ON_NO_RESPONSE_ACK_METHOD, A.RECORD_STATUS, A.UPDATE_COUNT, A.CREATOR_ID, A.CREATE_DATE, A.UPDATER_ID, A.UPDATE_DATE ");
        sqlStat.append("FROM   WORKFLOW_STEP A ");
        sqlStat.append("WHERE  A.RECORD_STATUS = ? ");
        
        // Form the WHERE clause for filtering.
        if (searchForm.isSearchable()) {
          String searchField = getSearchColumn(searchForm.getBasicSearchField());
          sqlStat.append("AND  " + searchField + " " + searchForm.getBasicSearchType() + " ? ");
        }
        
        //format the sql for any 'LIKE' statement contained
        sqlStat = this.getFormattedSQL(sqlStat.toString());
        
        // Form the ORDER clause for sorting.
        if (searchForm.isSortable()) {
          String sortAttribute = searchForm.getSortAttribute();
          if (sortAttribute.indexOf(".") < 0) {
            sortAttribute = "A." + sortAttribute;
          }
          sqlStat.append("ORDER BY " + sortAttribute + " " + searchForm.getSortOrder());
        }
        
        // Get total number of record return.
        sqlStatCnt = this.getSelectCountSQL(sqlStat);
        preStatCnt = dbConn.prepareStatement(sqlStatCnt.toString());
        this.setPrepareStatement(preStatCnt, 1, GlobalConstant.RECORD_STATUS_ACTIVE);
        if (searchForm.isSearchable()) {
          String searchKeyword = this.getFormattedKeyword(searchForm.getBasicSearchKeyword(), searchForm.getBasicSearchType());
          this.setPrepareStatement(preStatCnt, 2, searchKeyword);
        }
        rsCnt = preStatCnt.executeQuery();
        if (rsCnt.next()) {
          totalNumOfRecord = rsCnt.getInt(1);
        }
        try { rsCnt.close(); } catch (Exception ignore) {} finally { rsCnt = null; }
        try { preStatCnt.close(); } catch (Exception ignore) {} finally { preStatCnt = null; }
        
        // Retrieve the result in row basis.
        sqlStat = this.getSelectListSQL(sqlStat, startOffset, pageSize);
        preStat = dbConn.prepareStatement(sqlStat.toString(), ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
        this.setPrepareStatement(preStat, 1, GlobalConstant.RECORD_STATUS_ACTIVE);
        if (searchForm.isSearchable()) {
          String searchKeyword = this.getFormattedKeyword(searchForm.getBasicSearchKeyword(), searchForm.getBasicSearchType());
          this.setPrepareStatement(preStat, 2, searchKeyword);
        }
        rs = preStat.executeQuery();
        
        this.positionCursor(rs, startOffset, pageSize);
        
        while (rs.next() && rowLoopCnt<pageSize) {
          WorkflowStep tmpWorkflowStep = new WorkflowStep();
          tmpWorkflowStep.setID(getResultSetInteger(rs, "ID"));
          tmpWorkflowStep.setWorkflowRecordID(getResultSetInteger(rs, "WORKFLOW_RECORD_ID"));
          tmpWorkflowStep.setStepSeq(getResultSetInteger(rs, "STEP_SEQ"));
          tmpWorkflowStep.setStepName(getResultSetString(rs, "STEP_NAME"));
          tmpWorkflowStep.setActionType(getResultSetString(rs, "ACTION_TYPE"));
          tmpWorkflowStep.setActionID(getResultSetInteger(rs, "ACTION_ID"));
          tmpWorkflowStep.setDescription(getResultSetString(rs, "DESCRIPTION"));
          tmpWorkflowStep.setWorkflowRule(getResultSetString(rs, "WORKFLOW_RULE"));
          tmpWorkflowStep.setSectionID(getResultSetString(rs, "FORM_SECTION_ID"));
          tmpWorkflowStep.setPriority(getResultSetString(rs, "PRIORITY"));
          tmpWorkflowStep.setAllowAssignDueDate(getResultSetString(rs, "ALLOW_ASSIGN_DUE_DATE"));
          tmpWorkflowStep.setAllowAssignPriority(getResultSetString(rs, "ALLOW_ASSIGN_PRIORITY"));
          tmpWorkflowStep.setAllowStop(getResultSetString(rs, "ALLOW_STOP"));
          tmpWorkflowStep.setOnCompleteAckMethod(getResultSetString(rs, "ON_COMPLETE_ACK_METHOD"));
          tmpWorkflowStep.setOnNoResponseFor(getResultSetInteger(rs, "ON_NO_RESPONSE_FOR"));
          tmpWorkflowStep.setOnNoResponseCalendarID(getResultSetString(rs, "ON_NO_RESPONSE_CALENDAR_ID"));
          tmpWorkflowStep.setOnNoResponseAfter(getResultSetTimestamp(rs, "ON_NO_RESPONSE_AFTER"));
          tmpWorkflowStep.setOnNoResponseAction(getResultSetString(rs, "ON_NO_RESPONSE_ACTION"));
          tmpWorkflowStep.setOnNoResponseNextStepID(getResultSetInteger(rs, "ON_NO_RESPONSE_NEXT_STEP_ID"));
          tmpWorkflowStep.setOnNoResponseAckMethod(getResultSetString(rs, "ON_NO_RESPONSE_ACK_METHOD"));
          tmpWorkflowStep.setFilterBy(getResultSetInteger(rs, "FILTER_BY"));
          tmpWorkflowStep.setRecordStatus(getResultSetString(rs, "RECORD_STATUS"));
          tmpWorkflowStep.setUpdateCount(getResultSetInteger(rs, "UPDATE_COUNT"));
          tmpWorkflowStep.setCreatorID(getResultSetInteger(rs, "CREATOR_ID"));
          tmpWorkflowStep.setCreateDate(getResultSetTimestamp(rs, "CREATE_DATE"));
          tmpWorkflowStep.setUpdaterID(getResultSetInteger(rs, "UPDATER_ID"));
          tmpWorkflowStep.setUpdateDate(getResultSetTimestamp(rs, "UPDATE_DATE"));
          tmpWorkflowStep.setCreatorName(UserInfoFactory.getUserFullName(tmpWorkflowStep.getCreatorID()));
          tmpWorkflowStep.setUpdaterName(UserInfoFactory.getUserFullName(tmpWorkflowStep.getUpdaterID()));
          tmpWorkflowStep.setRecordCount(totalNumOfRecord);
          tmpWorkflowStep.setRowNum(startOffset++);
          ++rowLoopCnt;
          result.add(tmpWorkflowStep);
        }
        return(result);
      } catch (ApplicationException appEx) {
        throw appEx;
      } catch (SQLException sqle) {
        log.error(sqle, sqle);
        throw new ApplicationException(ErrorConstant.DB_GENERAL_ERROR, sqle, sqle.toString());
      } catch (Exception e) {
        log.error(e, e);
        throw new ApplicationException(ErrorConstant.DB_SELECT_ERROR, e);
      } finally {
        try { rs.close(); } catch (Exception ignore) {} finally { rs = null; }
        try { preStat.close(); } catch (Exception ignore) {} finally { preStat = null; }
        try { rsCnt.close(); } catch (Exception ignore) {} finally { rsCnt = null; }
        try { preStatCnt.close(); } catch (Exception ignore) {} finally { preStatCnt = null; }
      }
    }
  }
  
  protected synchronized List getList() throws ApplicationException {
    PreparedStatement preStat = null;
    ResultSet rs = null;
    StringBuffer sqlStat = new StringBuffer();
    List result = new ArrayList();
    
    synchronized(dbConn) {
      try {
        sqlStat.append("SELECT A.ID, A.WORKFLOW_RECORD_ID, A.STEP_SEQ, A.STEP_NAME, A.ACTION_TYPE, A.ACTION_ID, A.DESCRIPTION, A.WORKFLOW_RULE, A.FORM_SECTION_ID, A.PRIORITY, A.ALLOW_ASSIGN_DUE_DATE, A.ALLOW_ASSIGN_PRIORITY, A.ALLOW_STOP, A.ON_COMPLETE_ACK_METHOD, A.FILTER_BY, A.ON_NO_RESPONSE_FOR, A.ON_NO_RESPONSE_CALENDAR_ID, A.ON_NO_RESPONSE_AFTER, A.ON_NO_RESPONSE_ACTION, A.ON_NO_RESPONSE_NEXT_STEP_ID, A.ON_NO_RESPONSE_ACK_METHOD, A.RECORD_STATUS, A.UPDATE_COUNT, A.CREATOR_ID, A.CREATE_DATE, A.UPDATER_ID, A.UPDATE_DATE ");
        sqlStat.append("FROM   WORKFLOW_STEP A ");
        sqlStat.append("WHERE  A.RECORD_STATUS = ? ");
        preStat = dbConn.prepareStatement(sqlStat.toString(), ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
        this.setPrepareStatement(preStat, 1, GlobalConstant.RECORD_STATUS_ACTIVE);
        rs = preStat.executeQuery();
        while (rs.next()) {
          WorkflowStep tmpWorkflowStep = new WorkflowStep();
          tmpWorkflowStep.setID(getResultSetInteger(rs, "ID"));
          tmpWorkflowStep.setWorkflowRecordID(getResultSetInteger(rs, "WORKFLOW_RECORD_ID"));
          tmpWorkflowStep.setStepSeq(getResultSetInteger(rs, "STEP_SEQ"));
          tmpWorkflowStep.setStepName(getResultSetString(rs, "STEP_NAME"));
          tmpWorkflowStep.setActionType(getResultSetString(rs, "ACTION_TYPE"));
          tmpWorkflowStep.setActionID(getResultSetInteger(rs, "ACTION_ID"));
          tmpWorkflowStep.setDescription(getResultSetString(rs, "DESCRIPTION"));
          tmpWorkflowStep.setWorkflowRule(getResultSetString(rs, "WORKFLOW_RULE"));
          tmpWorkflowStep.setSectionID(getResultSetString(rs, "FORM_SECTION_ID"));
          tmpWorkflowStep.setPriority(getResultSetString(rs, "PRIORITY"));
          tmpWorkflowStep.setAllowAssignDueDate(getResultSetString(rs, "ALLOW_ASSIGN_DUE_DATE"));
          tmpWorkflowStep.setAllowAssignPriority(getResultSetString(rs, "ALLOW_ASSIGN_PRIORITY"));
          tmpWorkflowStep.setAllowStop(getResultSetString(rs, "ALLOW_STOP"));
          tmpWorkflowStep.setOnCompleteAckMethod(getResultSetString(rs, "ON_COMPLETE_ACK_METHOD"));
          tmpWorkflowStep.setOnNoResponseFor(getResultSetInteger(rs, "ON_NO_RESPONSE_FOR"));
          tmpWorkflowStep.setOnNoResponseCalendarID(getResultSetString(rs, "ON_NO_RESPONSE_CALENDAR_ID"));
          tmpWorkflowStep.setOnNoResponseAfter(getResultSetTimestamp(rs, "ON_NO_RESPONSE_AFTER"));
          tmpWorkflowStep.setOnNoResponseAction(getResultSetString(rs, "ON_NO_RESPONSE_ACTION"));
          tmpWorkflowStep.setOnNoResponseNextStepID(getResultSetInteger(rs, "ON_NO_RESPONSE_NEXT_STEP_ID"));
          tmpWorkflowStep.setOnNoResponseAckMethod(getResultSetString(rs, "ON_NO_RESPONSE_ACK_METHOD"));
          tmpWorkflowStep.setFilterBy(getResultSetInteger(rs, "FILTER_BY"));
          tmpWorkflowStep.setRecordStatus(getResultSetString(rs, "RECORD_STATUS"));
          tmpWorkflowStep.setUpdateCount(getResultSetInteger(rs, "UPDATE_COUNT"));
          tmpWorkflowStep.setCreatorID(getResultSetInteger(rs, "CREATOR_ID"));
          tmpWorkflowStep.setCreateDate(getResultSetTimestamp(rs, "CREATE_DATE"));
          tmpWorkflowStep.setUpdaterID(getResultSetInteger(rs, "UPDATER_ID"));
          tmpWorkflowStep.setUpdateDate(getResultSetTimestamp(rs, "UPDATE_DATE"));
          tmpWorkflowStep.setCreatorName(UserInfoFactory.getUserFullName(tmpWorkflowStep.getCreatorID()));
          tmpWorkflowStep.setUpdaterName(UserInfoFactory.getUserFullName(tmpWorkflowStep.getUpdaterID()));
          result.add(tmpWorkflowStep);
        }
        return(result);
      } catch (ApplicationException appEx) {
        throw appEx;
      } catch (SQLException sqle) {
        log.error(sqle, sqle);
        throw new ApplicationException(ErrorConstant.DB_GENERAL_ERROR, sqle, sqle.toString());
      } catch (Exception e) {
        log.error(e, e);
        throw new ApplicationException(ErrorConstant.DB_SELECT_ERROR, e);
      } finally {
        try { rs.close(); } catch (Exception ignore) {} finally { rs = null; }
        try { preStat.close(); } catch (Exception ignore) {} finally { preStat = null; }
      }
    }
  }
  
  protected void validateInsert(AbstractBaseObject obj) throws ApplicationException {
    WorkflowStep tmpWorkflowStep = (WorkflowStep)((WorkflowStep)obj).clone();
    StringBuffer sbSqlStat = new StringBuffer();
    PreparedStatement pstat = null;
    ResultSet rs = null;
    
    // Check duplicate user group name in the same group type.
    sbSqlStat.append("SELECT COUNT(*) ");
    sbSqlStat.append("FROM   WORKFLOW_STEP ");
    sbSqlStat.append("WHERE WORKFLOW_RECORD_ID = ? AND STEP_SEQ = ? AND  RECORD_STATUS = ? ");
    
    try {
      pstat = this.dbConn.prepareStatement(sbSqlStat.toString());
      this.setPrepareStatement(pstat, 1, tmpWorkflowStep.getWorkflowRecordID());
      this.setPrepareStatement(pstat, 2, tmpWorkflowStep.getStepSeq());
      this.setPrepareStatement(pstat, 3, GlobalConstant.RECORD_STATUS_ACTIVE);
      rs = pstat.executeQuery();
      if (rs.next() && rs.getInt(1)>0) {
        throw new ApplicationException(com.dcivision.workflow.WorkflowErrorConstant.DUPLICATE_STEP_SEQ);
      }
    } catch (java.sql.SQLException sqle) {
      throw new ApplicationException(ErrorConstant.DB_GENERAL_ERROR, sqle, sqle.toString());
    } finally {
      try { rs.close(); } catch (Exception ignore) {} finally { rs = null; }
      try { pstat.close(); } catch (Exception ignore) {} finally { pstat = null; }
    }
  }
  
  protected void validateUpdate(AbstractBaseObject obj) throws ApplicationException {
    WorkflowStep tmpWorkflowStep = (WorkflowStep)((WorkflowStep)obj).clone();
    StringBuffer sbSqlStat = new StringBuffer();
    PreparedStatement pstat = null;
    ResultSet rs = null;
    
    // Check duplicate user group name in the same group type.
    sbSqlStat.append("SELECT COUNT(*) ");
    sbSqlStat.append("FROM   WORKFLOW_STEP ");
    sbSqlStat.append("WHERE WORKFLOW_RECORD_ID = ? AND STEP_SEQ = ? AND  RECORD_STATUS = ? AND ID <> ? ");
    
    try {
      pstat = this.dbConn.prepareStatement(sbSqlStat.toString());
      this.setPrepareStatement(pstat, 1, tmpWorkflowStep.getWorkflowRecordID());
      this.setPrepareStatement(pstat, 2, tmpWorkflowStep.getStepSeq());
      this.setPrepareStatement(pstat, 3, GlobalConstant.RECORD_STATUS_ACTIVE);
      this.setPrepareStatement(pstat, 4, tmpWorkflowStep.getID());
      rs = pstat.executeQuery();
      if (rs.next() && rs.getInt(1)>0) {
        throw new ApplicationException(com.dcivision.workflow.WorkflowErrorConstant.DUPLICATE_STEP_SEQ);
      }
    } catch (java.sql.SQLException sqle) {
      throw new ApplicationException(ErrorConstant.DB_GENERAL_ERROR, sqle, sqle.toString());
    } finally {
      try { rs.close(); } catch (Exception ignore) {} finally { rs = null; }
      try { pstat.close(); } catch (Exception ignore) {} finally { pstat = null; }
    }
  }
  
  protected void validateDelete(AbstractBaseObject obj) throws ApplicationException {
  }
  
  protected synchronized AbstractBaseObject insert(AbstractBaseObject obj) throws ApplicationException {
    PreparedStatement preStat = null;
    StringBuffer sqlStat = new StringBuffer();
    WorkflowStep tmpWorkflowStep = (WorkflowStep)((WorkflowStep)obj).clone();
    
    synchronized(dbConn) {
      try {
        Integer nextID = getNextPrimaryID();
        Timestamp currTime = Utility.getCurrentTimestamp();
        sqlStat.append("INSERT ");
        sqlStat.append("INTO   WORKFLOW_STEP(ID, WORKFLOW_RECORD_ID, STEP_SEQ, STEP_NAME, ACTION_TYPE, ACTION_ID, DESCRIPTION, WORKFLOW_RULE, PRIORITY, ALLOW_ASSIGN_DUE_DATE, ALLOW_ASSIGN_PRIORITY, ALLOW_STOP, ON_COMPLETE_ACK_METHOD, ON_NO_RESPONSE_FOR, ON_NO_RESPONSE_CALENDAR_ID, ON_NO_RESPONSE_AFTER, ON_NO_RESPONSE_ACTION, ON_NO_RESPONSE_NEXT_STEP_ID, ON_NO_RESPONSE_ACK_METHOD, FILTER_BY, RECORD_STATUS, UPDATE_COUNT, CREATOR_ID, CREATE_DATE, UPDATER_ID, UPDATE_DATE,FORM_SECTION_ID) ");
        sqlStat.append("VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) ");
        preStat = dbConn.prepareStatement(sqlStat.toString(), ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
        setPrepareStatement(preStat, 1, nextID);
        setPrepareStatement(preStat, 2, tmpWorkflowStep.getWorkflowRecordID());
        setPrepareStatement(preStat, 3, tmpWorkflowStep.getStepSeq());
        setPrepareStatement(preStat, 4, tmpWorkflowStep.getStepName());
        setPrepareStatement(preStat, 5, tmpWorkflowStep.getActionType());
        setPrepareStatement(preStat, 6, tmpWorkflowStep.getActionID());
        setPrepareStatement(preStat, 7, tmpWorkflowStep.getDescription());
        setPrepareStatement(preStat, 8, tmpWorkflowStep.getWorkflowRule());
        setPrepareStatement(preStat, 9, tmpWorkflowStep.getPriority());
        setPrepareStatement(preStat, 10, tmpWorkflowStep.getAllowAssignDueDate());
        setPrepareStatement(preStat, 11, tmpWorkflowStep.getAllowAssignPriority());
        setPrepareStatement(preStat, 12, tmpWorkflowStep.getAllowStop());
        setPrepareStatement(preStat, 13, tmpWorkflowStep.getOnCompleteAckMethod());
        setPrepareStatement(preStat, 14, tmpWorkflowStep.getOnNoResponseFor());
        if("".equals(tmpWorkflowStep.getOnNoResponseCalendarID())){
        	tmpWorkflowStep.setOnNoResponseCalendarID(null);
        }//sliever.hu.udpate for database bug
        setPrepareStatement(preStat, 15, tmpWorkflowStep.getOnNoResponseCalendarID());
        setPrepareStatement(preStat, 16, tmpWorkflowStep.getOnNoResponseAfter());
        setPrepareStatement(preStat, 17, tmpWorkflowStep.getOnNoResponseAction());
        setPrepareStatement(preStat, 18, tmpWorkflowStep.getOnNoResponseNextStepID());
        setPrepareStatement(preStat, 19, tmpWorkflowStep.getOnNoResponseAckMethod());
        setPrepareStatement(preStat, 20, tmpWorkflowStep.getFilterBy());
        setPrepareStatement(preStat, 21, GlobalConstant.RECORD_STATUS_ACTIVE);
        setPrepareStatement(preStat, 22, new Integer(0));
        setPrepareStatement(preStat, 23, sessionContainer.getUserRecordID());
        setPrepareStatement(preStat, 24, currTime);
        setPrepareStatement(preStat, 25, sessionContainer.getUserRecordID());
        setPrepareStatement(preStat, 26, currTime);
        setPrepareStatement(preStat, 27, tmpWorkflowStep.getSectionID());
        preStat.executeUpdate();
        tmpWorkflowStep.setID(nextID);
        tmpWorkflowStep.setCreatorID(sessionContainer.getUserRecordID());
        tmpWorkflowStep.setCreateDate(currTime);
        tmpWorkflowStep.setUpdaterID(sessionContainer.getUserRecordID());
        tmpWorkflowStep.setUpdateDate(currTime);
        tmpWorkflowStep.setUpdateCount(new Integer(0));
        tmpWorkflowStep.setCreatorName(UserInfoFactory.getUserFullName(tmpWorkflowStep.getCreatorID()));
        tmpWorkflowStep.setUpdaterName(UserInfoFactory.getUserFullName(tmpWorkflowStep.getUpdaterID()));
        return(tmpWorkflowStep);
      } catch (ApplicationException appEx) {
        throw appEx;
      } catch (SQLException sqle) {
        log.error(sqle, sqle);
        throw new ApplicationException(ErrorConstant.DB_GENERAL_ERROR, sqle, sqle.toString());
      } catch (Exception e) {
        log.error(e, e);
        throw new ApplicationException(ErrorConstant.DB_INSERT_ERROR, e);
      } finally {
        try { preStat.close(); } catch (Exception ignore) {} finally { preStat = null; }
      }
    }
  }
  
  protected synchronized AbstractBaseObject update(AbstractBaseObject obj) throws ApplicationException {
    PreparedStatement preStat = null;
    StringBuffer sqlStat = new StringBuffer();
    WorkflowStep tmpWorkflowStep = (WorkflowStep)((WorkflowStep)obj).clone();
    
    synchronized(dbConn) {
      try {
        int updateCnt = 0;
        Timestamp currTime = Utility.getCurrentTimestamp();
        sqlStat.append("UPDATE WORKFLOW_STEP ");
        sqlStat.append("SET  WORKFLOW_RECORD_ID=?, STEP_SEQ=?, STEP_NAME=?, ACTION_TYPE=?, ACTION_ID=?, DESCRIPTION=?, WORKFLOW_RULE=?, PRIORITY=?, ALLOW_ASSIGN_DUE_DATE=?, ALLOW_ASSIGN_PRIORITY=?, ALLOW_STOP=?, ON_COMPLETE_ACK_METHOD=?, ON_NO_RESPONSE_FOR=?, ON_NO_RESPONSE_CALENDAR_ID=?, ON_NO_RESPONSE_AFTER=?, ON_NO_RESPONSE_ACTION=?, ON_NO_RESPONSE_NEXT_STEP_ID=?, ON_NO_RESPONSE_ACK_METHOD=?, FILTER_BY=?, UPDATE_COUNT=?, UPDATER_ID=?, UPDATE_DATE=?,FORM_SECTION_ID=? ");
        sqlStat.append("WHERE  ID=? AND UPDATE_COUNT=? ");
        preStat = dbConn.prepareStatement(sqlStat.toString(), ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
        setPrepareStatement(preStat, 1, tmpWorkflowStep.getWorkflowRecordID());
        setPrepareStatement(preStat, 2, tmpWorkflowStep.getStepSeq());
        setPrepareStatement(preStat, 3, tmpWorkflowStep.getStepName());
        setPrepareStatement(preStat, 4, tmpWorkflowStep.getActionType());
        setPrepareStatement(preStat, 5, tmpWorkflowStep.getActionID());
        setPrepareStatement(preStat, 6, tmpWorkflowStep.getDescription());
        setPrepareStatement(preStat, 7, tmpWorkflowStep.getWorkflowRule());
        setPrepareStatement(preStat, 8, tmpWorkflowStep.getPriority());
        setPrepareStatement(preStat, 9, tmpWorkflowStep.getAllowAssignDueDate());
        setPrepareStatement(preStat, 10, tmpWorkflowStep.getAllowAssignPriority());
        setPrepareStatement(preStat, 11, tmpWorkflowStep.getAllowStop());
        setPrepareStatement(preStat, 12, tmpWorkflowStep.getOnCompleteAckMethod());
        setPrepareStatement(preStat, 13, tmpWorkflowStep.getOnNoResponseFor());
        if("".equals(tmpWorkflowStep.getOnNoResponseCalendarID())){
        	tmpWorkflowStep.setOnNoResponseCalendarID(null);
        }
        //sliever.hu.udpate for database bug
        setPrepareStatement(preStat, 14, tmpWorkflowStep.getOnNoResponseCalendarID());
        setPrepareStatement(preStat, 15, tmpWorkflowStep.getOnNoResponseAfter());
        setPrepareStatement(preStat, 16, tmpWorkflowStep.getOnNoResponseAction());
        setPrepareStatement(preStat, 17, tmpWorkflowStep.getOnNoResponseNextStepID());
        setPrepareStatement(preStat, 18, tmpWorkflowStep.getOnNoResponseAckMethod());
        setPrepareStatement(preStat, 19, tmpWorkflowStep.getFilterBy());
        setPrepareStatement(preStat, 20, new Integer(tmpWorkflowStep.getUpdateCount().intValue() + 1));
        setPrepareStatement(preStat, 21, sessionContainer.getUserRecordID());
        setPrepareStatement(preStat, 22, currTime);
        setPrepareStatement(preStat, 23, tmpWorkflowStep.getSectionID() );
        setPrepareStatement(preStat, 24, tmpWorkflowStep.getID());
        setPrepareStatement(preStat, 25, tmpWorkflowStep.getUpdateCount());
        
        updateCnt = preStat.executeUpdate();
        if (updateCnt == 0) {
          throw new ApplicationException(ErrorConstant.DB_CONCURRENT_ERROR);
        } else {
          tmpWorkflowStep.setUpdaterID(sessionContainer.getUserRecordID());
          tmpWorkflowStep.setUpdateDate(currTime);
          tmpWorkflowStep.setUpdateCount(new Integer(tmpWorkflowStep.getUpdateCount().intValue() + 1));
          tmpWorkflowStep.setCreatorName(UserInfoFactory.getUserFullName(tmpWorkflowStep.getCreatorID()));
          tmpWorkflowStep.setUpdaterName(UserInfoFactory.getUserFullName(tmpWorkflowStep.getUpdaterID()));
          return(tmpWorkflowStep);
        }
      } catch (ApplicationException appEx) {
        throw appEx;
      } catch (SQLException sqle) {
        log.error(sqle, sqle);
        throw new ApplicationException(ErrorConstant.DB_GENERAL_ERROR, sqle, sqle.toString());
      } catch (Exception e) {
        log.error(e, e);
        throw new ApplicationException(ErrorConstant.DB_UPDATE_ERROR, e);
      } finally {
        try { preStat.close(); } catch (Exception ignore) {} finally { preStat = null; }
      }
    }
  }
  
  protected synchronized AbstractBaseObject delete(AbstractBaseObject obj) throws ApplicationException {
    PreparedStatement preStat = null;
    StringBuffer sqlStat = new StringBuffer();
    WorkflowStep tmpWorkflowStep = (WorkflowStep)((WorkflowStep)obj).clone();
    
    synchronized(dbConn) {
      try {
        int updateCnt = 0;
        sqlStat.append("DELETE ");
        sqlStat.append("FROM   WORKFLOW_STEP ");
        sqlStat.append("WHERE  ID=? AND UPDATE_COUNT=? ");
        preStat = dbConn.prepareStatement(sqlStat.toString(), ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
        setPrepareStatement(preStat, 1, tmpWorkflowStep.getID());
        setPrepareStatement(preStat, 2, tmpWorkflowStep.getUpdateCount());
        updateCnt = preStat.executeUpdate();
        if (updateCnt == 0) {
          throw new ApplicationException(ErrorConstant.DB_CONCURRENT_ERROR);
        } else {
          return(tmpWorkflowStep);
        }
      } catch (ApplicationException appEx) {
        throw appEx;
      } catch (SQLException sqle) {
        log.error(sqle, sqle);
        throw new ApplicationException(ErrorConstant.DB_GENERAL_ERROR, sqle, sqle.toString());
      } catch (Exception e) {
        log.error(e, e);
        throw new ApplicationException(ErrorConstant.DB_DELETE_ERROR, e);
      } finally {
        try { preStat.close(); } catch (Exception ignore) {} finally { preStat = null; }
      }
    }
  }
  
  protected synchronized void auditTrail(String opMode, AbstractBaseObject obj) throws ApplicationException {
    Vector oldValues = new Vector();
    Vector newValues = new Vector();
    WorkflowStep tmpWorkflowStep = (WorkflowStep)this.oldValue;
    
    if (tmpWorkflowStep != null) {
      oldValues.add(toAuditTrailValue(tmpWorkflowStep.getID()));
      oldValues.add(toAuditTrailValue(tmpWorkflowStep.getWorkflowRecordID()));
      oldValues.add(toAuditTrailValue(tmpWorkflowStep.getStepSeq()));
      oldValues.add(toAuditTrailValue(tmpWorkflowStep.getStepName()));
      oldValues.add(toAuditTrailValue(tmpWorkflowStep.getActionType()));
      oldValues.add(toAuditTrailValue(tmpWorkflowStep.getActionID()));
      oldValues.add(toAuditTrailValue(tmpWorkflowStep.getDescription()));
      oldValues.add(toAuditTrailValue(tmpWorkflowStep.getWorkflowRule()));
      oldValues.add(toAuditTrailValue(tmpWorkflowStep.getSectionID()))   ;
      oldValues.add(toAuditTrailValue(tmpWorkflowStep.getPriority()));
      oldValues.add(toAuditTrailValue(tmpWorkflowStep.getAllowAssignDueDate()));
      oldValues.add(toAuditTrailValue(tmpWorkflowStep.getAllowAssignPriority()));
      oldValues.add(toAuditTrailValue(tmpWorkflowStep.getParentWorkflowStepType()));
      oldValues.add(toAuditTrailValue(tmpWorkflowStep.getOnCompleteAckMethod()));
      oldValues.add(toAuditTrailValue(tmpWorkflowStep.getOnNoResponseFor()));
      oldValues.add(toAuditTrailValue(tmpWorkflowStep.getOnNoResponseCalendarID()));
      oldValues.add(toAuditTrailValue(tmpWorkflowStep.getOnNoResponseAfter()));
      oldValues.add(toAuditTrailValue(tmpWorkflowStep.getOnNoResponseAction()));
      oldValues.add(toAuditTrailValue(tmpWorkflowStep.getOnNoResponseNextStepID()));
      oldValues.add(toAuditTrailValue(tmpWorkflowStep.getOnNoResponseAckMethod()));
      oldValues.add(toAuditTrailValue(tmpWorkflowStep.getFilterBy()));
      oldValues.add(toAuditTrailValue(tmpWorkflowStep.getAllowStop()));
      oldValues.add(toAuditTrailValue(tmpWorkflowStep.getRecordStatus()));
      oldValues.add(toAuditTrailValue(tmpWorkflowStep.getUpdateCount()));
      oldValues.add(toAuditTrailValue(tmpWorkflowStep.getCreatorID()));
      oldValues.add(toAuditTrailValue(tmpWorkflowStep.getCreateDate()));
      oldValues.add(toAuditTrailValue(tmpWorkflowStep.getUpdaterID()));
      oldValues.add(toAuditTrailValue(tmpWorkflowStep.getUpdateDate()));
    }
    
    tmpWorkflowStep = (WorkflowStep)obj;
    if (tmpWorkflowStep != null) {
      newValues.add(toAuditTrailValue(tmpWorkflowStep.getID()));
      newValues.add(toAuditTrailValue(tmpWorkflowStep.getWorkflowRecordID()));
      newValues.add(toAuditTrailValue(tmpWorkflowStep.getStepSeq()));
      newValues.add(toAuditTrailValue(tmpWorkflowStep.getStepName()));
      newValues.add(toAuditTrailValue(tmpWorkflowStep.getActionType()));
      newValues.add(toAuditTrailValue(tmpWorkflowStep.getActionID()));
      newValues.add(toAuditTrailValue(tmpWorkflowStep.getDescription()));
      newValues.add(toAuditTrailValue(tmpWorkflowStep.getWorkflowRule()));
      newValues.add(toAuditTrailValue(tmpWorkflowStep.getSectionID()));
      newValues.add(toAuditTrailValue(tmpWorkflowStep.getPriority()));
      newValues.add(toAuditTrailValue(tmpWorkflowStep.getAllowAssignDueDate()));
      newValues.add(toAuditTrailValue(tmpWorkflowStep.getAllowAssignPriority()));
      newValues.add(toAuditTrailValue(tmpWorkflowStep.getOnCompleteAckMethod()));
      newValues.add(toAuditTrailValue(tmpWorkflowStep.getOnNoResponseFor()));
      newValues.add(toAuditTrailValue(tmpWorkflowStep.getOnNoResponseCalendarID()));
      newValues.add(toAuditTrailValue(tmpWorkflowStep.getOnNoResponseAfter()));
      newValues.add(toAuditTrailValue(tmpWorkflowStep.getOnNoResponseAction()));
      newValues.add(toAuditTrailValue(tmpWorkflowStep.getOnNoResponseNextStepID()));
      newValues.add(toAuditTrailValue(tmpWorkflowStep.getOnNoResponseAckMethod()));
      newValues.add(toAuditTrailValue(tmpWorkflowStep.getFilterBy()));
      newValues.add(toAuditTrailValue(tmpWorkflowStep.getAllowStop()));
      newValues.add(toAuditTrailValue(tmpWorkflowStep.getRecordStatus()));
      newValues.add(toAuditTrailValue(tmpWorkflowStep.getUpdateCount()));
      newValues.add(toAuditTrailValue(tmpWorkflowStep.getCreatorID()));
      newValues.add(toAuditTrailValue(tmpWorkflowStep.getCreateDate()));
      newValues.add(toAuditTrailValue(tmpWorkflowStep.getUpdaterID()));
      newValues.add(toAuditTrailValue(tmpWorkflowStep.getUpdateDate()));
    }
    
    auditTrailBase(opMode, oldValues, newValues);
  }
  
  /***********************************************************************
   * DON'T Modify the codes above unless you know what you are doing!!!  *
   * Put your own functions beblow.                                      *
   * For FINDER methods, the function name should be in the notation:    *
   *   public Object getObjectBy<Criteria>()                             *
   *   - e.g. public Object getObjectByCode()                            *
   *   public List getListBy<Criteria>()                                 *
   *   - e.g. public List getListByUserID()                              *
   * For OPERATION methods, the function name should be in the notation: *
   *   public void <Operation>ObjectBy<Criteria>()                       *
   *   - e.g. public void deleteObjectByCode()                           *
   *   public void <Operation>ListBy<Criteria>()                         *
   *   - e.g. public void deleteListByUserID()                           *
   ***********************************************************************/
  /**
   * Gets all the workflow steps of the given workflow record
   * 
   * @param workflowRecordID The workflow record ID
   * @return List of WorkflowStep objects
   * @throws ApplicationException
   * @see com.dcivision.workflow.bean.WorkflowStep
   * @see com.dcivision.workflow.bean.WorkflowRecord
   */
  public synchronized List getListByWorkflowRecordID(Integer workflowRecordID) throws ApplicationException {
    PreparedStatement preStat = null;
    ResultSet rs = null;
    StringBuffer sqlStat = new StringBuffer();
    List result = new ArrayList();
    
    synchronized(dbConn) {
      try {
        sqlStat.append("SELECT    A.ID, A.WORKFLOW_RECORD_ID, A.STEP_SEQ, A.STEP_NAME, A.ACTION_TYPE, A.ACTION_ID, A.DESCRIPTION, A.WORKFLOW_RULE, A.FORM_SECTION_ID, A.PRIORITY, A.ALLOW_ASSIGN_DUE_DATE, A.ALLOW_ASSIGN_PRIORITY, A.ALLOW_STOP, A.ON_COMPLETE_ACK_METHOD, A.FILTER_BY, A.ON_NO_RESPONSE_FOR, A.ON_NO_RESPONSE_CALENDAR_ID, A.ON_NO_RESPONSE_AFTER, A.ON_NO_RESPONSE_ACTION, A.ON_NO_RESPONSE_NEXT_STEP_ID, A.ON_NO_RESPONSE_ACK_METHOD, A.RECORD_STATUS, A.UPDATE_COUNT, A.CREATOR_ID, A.CREATE_DATE, A.UPDATER_ID, A.UPDATE_DATE ");
        sqlStat.append("FROM      WORKFLOW_STEP A ");
        sqlStat.append("WHERE     A.RECORD_STATUS = ? AND A.WORKFLOW_RECORD_ID = ? ");
        sqlStat.append("ORDER BY  A.ID ASC ");
        preStat = dbConn.prepareStatement(sqlStat.toString(), ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
        this.setPrepareStatement(preStat, 1, GlobalConstant.RECORD_STATUS_ACTIVE);
        this.setPrepareStatement(preStat, 2, workflowRecordID);
        rs = preStat.executeQuery();
        while (rs.next()) {
          WorkflowStep tmpWorkflowStep = new WorkflowStep();
          tmpWorkflowStep.setID(getResultSetInteger(rs, "ID"));
          tmpWorkflowStep.setWorkflowRecordID(getResultSetInteger(rs, "WORKFLOW_RECORD_ID"));
          tmpWorkflowStep.setStepSeq(getResultSetInteger(rs, "STEP_SEQ"));
          tmpWorkflowStep.setStepName(getResultSetString(rs, "STEP_NAME"));
          tmpWorkflowStep.setActionType(getResultSetString(rs, "ACTION_TYPE"));
          tmpWorkflowStep.setActionID(getResultSetInteger(rs, "ACTION_ID"));
          tmpWorkflowStep.setDescription(getResultSetString(rs, "DESCRIPTION"));
          tmpWorkflowStep.setWorkflowRule(getResultSetString(rs, "WORKFLOW_RULE"));
          tmpWorkflowStep.setSectionID(getResultSetString(rs, "FORM_SECTION_ID"));
          tmpWorkflowStep.setPriority(getResultSetString(rs, "PRIORITY"));
          tmpWorkflowStep.setAllowAssignDueDate(getResultSetString(rs, "ALLOW_ASSIGN_DUE_DATE"));
          tmpWorkflowStep.setAllowAssignPriority(getResultSetString(rs, "ALLOW_ASSIGN_PRIORITY"));
          tmpWorkflowStep.setAllowStop(getResultSetString(rs, "ALLOW_STOP"));
          tmpWorkflowStep.setOnCompleteAckMethod(getResultSetString(rs, "ON_COMPLETE_ACK_METHOD"));
          tmpWorkflowStep.setOnNoResponseFor(getResultSetInteger(rs, "ON_NO_RESPONSE_FOR"));
          tmpWorkflowStep.setOnNoResponseCalendarID(getResultSetString(rs, "ON_NO_RESPONSE_CALENDAR_ID"));
          tmpWorkflowStep.setOnNoResponseAfter(getResultSetTimestamp(rs, "ON_NO_RESPONSE_AFTER"));
          tmpWorkflowStep.setOnNoResponseAction(getResultSetString(rs, "ON_NO_RESPONSE_ACTION"));
          tmpWorkflowStep.setOnNoResponseNextStepID(getResultSetInteger(rs, "ON_NO_RESPONSE_NEXT_STEP_ID"));
          tmpWorkflowStep.setOnNoResponseAckMethod(getResultSetString(rs, "ON_NO_RESPONSE_ACK_METHOD"));
          tmpWorkflowStep.setFilterBy(getResultSetInteger(rs, "FILTER_BY"));
          tmpWorkflowStep.setRecordStatus(getResultSetString(rs, "RECORD_STATUS"));
          tmpWorkflowStep.setUpdateCount(getResultSetInteger(rs, "UPDATE_COUNT"));
          tmpWorkflowStep.setCreatorID(getResultSetInteger(rs, "CREATOR_ID"));
          tmpWorkflowStep.setCreateDate(getResultSetTimestamp(rs, "CREATE_DATE"));
          tmpWorkflowStep.setUpdaterID(getResultSetInteger(rs, "UPDATER_ID"));
          tmpWorkflowStep.setUpdateDate(getResultSetTimestamp(rs, "UPDATE_DATE"));
          tmpWorkflowStep.setCreatorName(UserInfoFactory.getUserFullName(tmpWorkflowStep.getCreatorID()));
          tmpWorkflowStep.setUpdaterName(UserInfoFactory.getUserFullName(tmpWorkflowStep.getUpdaterID()));
          result.add(tmpWorkflowStep);
        }
        return(result);
      } catch (ApplicationException appEx) {
        throw appEx;
      } catch (SQLException sqle) {
        log.error(sqle, sqle);
        throw new ApplicationException(ErrorConstant.DB_GENERAL_ERROR, sqle, sqle.toString());
      } catch (Exception e) {
        log.error(e, e);
        throw new ApplicationException(ErrorConstant.DB_SELECT_ERROR, e);
      } finally {
        try { rs.close(); } catch (Exception ignore) {} finally { rs = null; }
        try { preStat.close(); } catch (Exception ignore) {} finally { preStat = null; }
      }
    }
  }
  
  /**
   * tc.add
   * @param workflowRecordID
   * @return
   * @throws ApplicationException
   */
  public synchronized List getListByWorkflowRecordIDAndActorType(Integer workflowRecordID,String actorType) throws ApplicationException {
    PreparedStatement preStat = null;
    ResultSet rs = null;
    StringBuffer sqlStat = new StringBuffer();
    List result = new ArrayList();

    synchronized(dbConn) {
      try {
        sqlStat.append("SELECT    DISTINCT A.ID, A.WORKFLOW_RECORD_ID, A.STEP_SEQ, A.STEP_NAME, A.ACTION_TYPE, A.ACTION_ID, A.DESCRIPTION, A.WORKFLOW_RULE, A.FORM_SECTION_ID, A.PRIORITY, A.ALLOW_ASSIGN_DUE_DATE, A.ALLOW_ASSIGN_PRIORITY, A.ALLOW_STOP, A.ON_COMPLETE_ACK_METHOD, A.FILTER_BY, A.ON_NO_RESPONSE_FOR, A.ON_NO_RESPONSE_AFTER, A.ON_NO_RESPONSE_ACTION, A.ON_NO_RESPONSE_NEXT_STEP_ID, A.ON_NO_RESPONSE_ACK_METHOD, A.RECORD_STATUS, A.UPDATE_COUNT, A.CREATOR_ID, A.CREATE_DATE, A.UPDATER_ID, A.UPDATE_DATE ");
        sqlStat.append("FROM      WORKFLOW_STEP A , MTM_WF_STEP_USER_ACTOR B ");
        sqlStat.append("WHERE     A.RECORD_STATUS = ? AND A.WORKFLOW_RECORD_ID = ? AND B.ACTOR_TYPE = ? AND B.RECORD_STATUS = ? AND A.ID = B.WORKFLOW_STEP_ID ");
        sqlStat.append("ORDER BY  A.ID ASC ");
        preStat = dbConn.prepareStatement(sqlStat.toString(), ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
        this.setPrepareStatement(preStat, 1, GlobalConstant.RECORD_STATUS_ACTIVE);
        this.setPrepareStatement(preStat, 2, workflowRecordID);
        this.setPrepareStatement(preStat, 3, actorType);
        this.setPrepareStatement(preStat, 4, GlobalConstant.RECORD_STATUS_ACTIVE);
        rs = preStat.executeQuery();
        while (rs.next()) {
          WorkflowStep tmpWorkflowStep = new WorkflowStep();
          tmpWorkflowStep.setID(getResultSetInteger(rs, "ID"));
          tmpWorkflowStep.setWorkflowRecordID(getResultSetInteger(rs, "WORKFLOW_RECORD_ID"));
          tmpWorkflowStep.setStepSeq(getResultSetInteger(rs, "STEP_SEQ"));
          tmpWorkflowStep.setStepName(getResultSetString(rs, "STEP_NAME"));
          tmpWorkflowStep.setActionType(getResultSetString(rs, "ACTION_TYPE"));
          tmpWorkflowStep.setActionID(getResultSetInteger(rs, "ACTION_ID"));
          tmpWorkflowStep.setDescription(getResultSetString(rs, "DESCRIPTION"));
          tmpWorkflowStep.setWorkflowRule(getResultSetString(rs, "WORKFLOW_RULE"));
          tmpWorkflowStep.setSectionID(getResultSetString(rs, "FORM_SECTION_ID"));
          tmpWorkflowStep.setPriority(getResultSetString(rs, "PRIORITY"));
          tmpWorkflowStep.setAllowAssignDueDate(getResultSetString(rs, "ALLOW_ASSIGN_DUE_DATE"));
          tmpWorkflowStep.setAllowAssignPriority(getResultSetString(rs, "ALLOW_ASSIGN_PRIORITY"));
          tmpWorkflowStep.setAllowAssignDueDate(getResultSetString(rs, "ALLOW_STOP"));
          tmpWorkflowStep.setOnCompleteAckMethod(getResultSetString(rs, "ON_COMPLETE_ACK_METHOD"));
          tmpWorkflowStep.setOnNoResponseFor(getResultSetInteger(rs, "ON_NO_RESPONSE_FOR"));
          tmpWorkflowStep.setOnNoResponseAfter(getResultSetTimestamp(rs, "ON_NO_RESPONSE_AFTER"));
          tmpWorkflowStep.setOnNoResponseAction(getResultSetString(rs, "ON_NO_RESPONSE_ACTION"));
          tmpWorkflowStep.setOnNoResponseNextStepID(getResultSetInteger(rs, "ON_NO_RESPONSE_NEXT_STEP_ID"));
          tmpWorkflowStep.setOnNoResponseAckMethod(getResultSetString(rs, "ON_NO_RESPONSE_ACK_METHOD"));
          tmpWorkflowStep.setFilterBy(getResultSetInteger(rs, "FILTER_BY"));
          tmpWorkflowStep.setRecordStatus(getResultSetString(rs, "RECORD_STATUS"));
          tmpWorkflowStep.setUpdateCount(getResultSetInteger(rs, "UPDATE_COUNT"));
          tmpWorkflowStep.setCreatorID(getResultSetInteger(rs, "CREATOR_ID"));
          tmpWorkflowStep.setCreateDate(getResultSetTimestamp(rs, "CREATE_DATE"));
          tmpWorkflowStep.setUpdaterID(getResultSetInteger(rs, "UPDATER_ID"));
          tmpWorkflowStep.setUpdateDate(getResultSetTimestamp(rs, "UPDATE_DATE"));
          tmpWorkflowStep.setCreatorName(UserInfoFactory.getUserFullName(tmpWorkflowStep.getCreatorID()));
          tmpWorkflowStep.setUpdaterName(UserInfoFactory.getUserFullName(tmpWorkflowStep.getUpdaterID()));
          tmpWorkflowStep.setAssignActorType(actorType);
          result.add(tmpWorkflowStep);
        }
        return(result);
      } catch (ApplicationException appEx) {
        throw appEx;
      } catch (SQLException sqle) {
        log.error(sqle, sqle);
        throw new ApplicationException(ErrorConstant.DB_GENERAL_ERROR, sqle, sqle.toString());
      } catch (Exception e) {
        log.error(e, e);
        throw new ApplicationException(ErrorConstant.DB_SELECT_ERROR, e);
      } finally {
        try { rs.close(); } catch (Exception ignore) {} finally { rs = null; }
        try { preStat.close(); } catch (Exception ignore) {} finally { preStat = null; }
      }
    }
  }
  
  /**
   * Gets the first step "Start Workflow" of the given workflow record
   * 
   * @param workflowRecordID The workflow record ID
   * @return The first workflow step
   * @throws ApplicationException
   * @see com.dcivision.workflow.bean.WorkflowStep
   */
  public synchronized AbstractBaseObject getSubmitStepByWorkflowRecordID(Integer workflowRecordID) throws ApplicationException {
    PreparedStatement preStat = null;
    ResultSet rs = null;
    StringBuffer sqlStat = new StringBuffer();
    List result = new ArrayList();
    
    synchronized(dbConn) {
      try {
        sqlStat.append("SELECT    A.ID, A.WORKFLOW_RECORD_ID, A.STEP_SEQ, A.STEP_NAME, A.ACTION_TYPE, A.ACTION_ID, A.DESCRIPTION, A.WORKFLOW_RULE, A.FORM_SECTION_ID, A.PRIORITY, A.ALLOW_ASSIGN_DUE_DATE, A.ALLOW_ASSIGN_PRIORITY, A.ALLOW_STOP, A.ON_COMPLETE_ACK_METHOD, A.FILTER_BY, A.ON_NO_RESPONSE_FOR, A.ON_NO_RESPONSE_CALENDAR_ID, A.ON_NO_RESPONSE_AFTER, A.ON_NO_RESPONSE_ACTION, A.ON_NO_RESPONSE_NEXT_STEP_ID, A.ON_NO_RESPONSE_ACK_METHOD, A.RECORD_STATUS, A.UPDATE_COUNT, A.CREATOR_ID, A.CREATE_DATE, A.UPDATER_ID, A.UPDATE_DATE ");
        sqlStat.append("FROM      WORKFLOW_STEP A ");
        sqlStat.append("WHERE     A.RECORD_STATUS = ? AND A.WORKFLOW_RECORD_ID = ? AND A.ACTION_TYPE = ? ");
        sqlStat.append("ORDER BY  A.ID ASC ");
        preStat = dbConn.prepareStatement(sqlStat.toString());
        this.setPrepareStatement(preStat, 1, GlobalConstant.RECORD_STATUS_ACTIVE);
        this.setPrepareStatement(preStat, 2, workflowRecordID);
        this.setPrepareStatement(preStat, 3, WorkflowStep.ACTION_TYPE_SUBMIT);
        rs = preStat.executeQuery();
        if (rs.next()) {
          WorkflowStep tmpWorkflowStep = new WorkflowStep();
          tmpWorkflowStep.setID(getResultSetInteger(rs, "ID"));
          tmpWorkflowStep.setWorkflowRecordID(getResultSetInteger(rs, "WORKFLOW_RECORD_ID"));
          tmpWorkflowStep.setStepSeq(getResultSetInteger(rs, "STEP_SEQ"));
          tmpWorkflowStep.setStepName(getResultSetString(rs, "STEP_NAME"));
          tmpWorkflowStep.setActionType(getResultSetString(rs, "ACTION_TYPE"));
          tmpWorkflowStep.setActionID(getResultSetInteger(rs, "ACTION_ID"));
          tmpWorkflowStep.setDescription(getResultSetString(rs, "DESCRIPTION"));
          tmpWorkflowStep.setWorkflowRule(getResultSetString(rs, "WORKFLOW_RULE"));
          tmpWorkflowStep.setSectionID(getResultSetString(rs, "FORM_SECTION_ID"));
          tmpWorkflowStep.setPriority(getResultSetString(rs, "PRIORITY"));
          tmpWorkflowStep.setAllowAssignDueDate(getResultSetString(rs, "ALLOW_ASSIGN_DUE_DATE"));
          tmpWorkflowStep.setAllowAssignPriority(getResultSetString(rs, "ALLOW_ASSIGN_PRIORITY"));
          tmpWorkflowStep.setAllowStop(getResultSetString(rs, "ALLOW_STOP"));
          tmpWorkflowStep.setOnCompleteAckMethod(getResultSetString(rs, "ON_COMPLETE_ACK_METHOD"));
          tmpWorkflowStep.setOnNoResponseFor(getResultSetInteger(rs, "ON_NO_RESPONSE_FOR"));
          tmpWorkflowStep.setOnNoResponseCalendarID(getResultSetString(rs, "ON_NO_RESPONSE_CALENDAR_ID"));
          tmpWorkflowStep.setOnNoResponseAfter(getResultSetTimestamp(rs, "ON_NO_RESPONSE_AFTER"));
          tmpWorkflowStep.setOnNoResponseAction(getResultSetString(rs, "ON_NO_RESPONSE_ACTION"));
          tmpWorkflowStep.setOnNoResponseNextStepID(getResultSetInteger(rs, "ON_NO_RESPONSE_NEXT_STEP_ID"));
          tmpWorkflowStep.setOnNoResponseAckMethod(getResultSetString(rs, "ON_NO_RESPONSE_ACK_METHOD"));
          tmpWorkflowStep.setFilterBy(getResultSetInteger(rs, "FILTER_BY"));
          tmpWorkflowStep.setRecordStatus(getResultSetString(rs, "RECORD_STATUS"));
          tmpWorkflowStep.setUpdateCount(getResultSetInteger(rs, "UPDATE_COUNT"));
          tmpWorkflowStep.setCreatorID(getResultSetInteger(rs, "CREATOR_ID"));
          tmpWorkflowStep.setCreateDate(getResultSetTimestamp(rs, "CREATE_DATE"));
          tmpWorkflowStep.setUpdaterID(getResultSetInteger(rs, "UPDATER_ID"));
          tmpWorkflowStep.setUpdateDate(getResultSetTimestamp(rs, "UPDATE_DATE"));
          tmpWorkflowStep.setCreatorName(UserInfoFactory.getUserFullName(tmpWorkflowStep.getCreatorID()));
          tmpWorkflowStep.setUpdaterName(UserInfoFactory.getUserFullName(tmpWorkflowStep.getUpdaterID()));
          return tmpWorkflowStep;
        } else {
          return null;
        }
      } catch (ApplicationException appEx) {
        throw appEx;
      } catch (SQLException sqle) {
        log.error(sqle, sqle);
        throw new ApplicationException(ErrorConstant.DB_GENERAL_ERROR, sqle, sqle.toString());
      } catch (Exception e) {
        log.error(e, e);
        throw new ApplicationException(ErrorConstant.DB_SELECT_ERROR, e);
      } finally {
        try { rs.close(); } catch (Exception ignore) {} finally { rs = null; }
        try { preStat.close(); } catch (Exception ignore) {} finally { preStat = null; }
      }
    }
  }
  
  /**
   * Gets the direct child step(s) of the given parent step
   * 
   * @param parentStepID The parent step id
   * @return List of child workflow steps
   * @throws ApplicationException
   * @see com.dcivision.workflow.bean.WorkflowStep
   */
  public synchronized List getListByParentStepID(Integer parentStepID) throws ApplicationException {
    PreparedStatement preStat = null;
    ResultSet rs = null;
    StringBuffer sqlStat = new StringBuffer();
    List result = new ArrayList();
    
    synchronized(dbConn) {
      try {
        sqlStat.append("SELECT    A.ID, A.WORKFLOW_RECORD_ID, A.STEP_SEQ, A.STEP_NAME, A.ACTION_TYPE, A.ACTION_ID, A.DESCRIPTION, A.WORKFLOW_RULE, A.FORM_SECTION_ID, A.PRIORITY, A.ALLOW_ASSIGN_DUE_DATE, A.ALLOW_ASSIGN_PRIORITY, A.ALLOW_STOP, A.ON_COMPLETE_ACK_METHOD, A.FILTER_BY, A.ON_NO_RESPONSE_FOR, A.ON_NO_RESPONSE_CALENDAR_ID, A.ON_NO_RESPONSE_AFTER, A.ON_NO_RESPONSE_ACTION, A.ON_NO_RESPONSE_NEXT_STEP_ID, A.ON_NO_RESPONSE_ACK_METHOD, A.RECORD_STATUS, A.UPDATE_COUNT, A.CREATOR_ID, A.CREATE_DATE, A.UPDATER_ID, A.UPDATE_DATE, B.PARENT_TYPE ");
        sqlStat.append("FROM      WORKFLOW_STEP A, MTM_WF_STEP_WF_STEP B ");
        sqlStat.append("WHERE     A.RECORD_STATUS = ? AND B.RECORD_STATUS = ? AND A.ID = B.CHILD_STEP_ID AND B.PARENT_STEP_ID = ? ");
        sqlStat.append("ORDER BY  A.ID ASC ");
        preStat = dbConn.prepareStatement(sqlStat.toString(), ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
        this.setPrepareStatement(preStat, 1, GlobalConstant.RECORD_STATUS_ACTIVE);
        this.setPrepareStatement(preStat, 2, GlobalConstant.RECORD_STATUS_ACTIVE);
        this.setPrepareStatement(preStat, 3, parentStepID);
        rs = preStat.executeQuery();
        while (rs.next()) {
          WorkflowStep tmpWorkflowStep = new WorkflowStep();
          tmpWorkflowStep.setID(getResultSetInteger(rs, "ID"));
          tmpWorkflowStep.setWorkflowRecordID(getResultSetInteger(rs, "WORKFLOW_RECORD_ID"));
          tmpWorkflowStep.setStepSeq(getResultSetInteger(rs, "STEP_SEQ"));
          tmpWorkflowStep.setStepName(getResultSetString(rs, "STEP_NAME"));
          tmpWorkflowStep.setActionType(getResultSetString(rs, "ACTION_TYPE"));
          tmpWorkflowStep.setActionID(getResultSetInteger(rs, "ACTION_ID"));
          tmpWorkflowStep.setDescription(getResultSetString(rs, "DESCRIPTION"));
          tmpWorkflowStep.setWorkflowRule(getResultSetString(rs, "WORKFLOW_RULE"));
          tmpWorkflowStep.setSectionID(getResultSetString(rs, "FORM_SECTION_ID"));
          tmpWorkflowStep.setPriority(getResultSetString(rs, "PRIORITY"));
          tmpWorkflowStep.setAllowAssignDueDate(getResultSetString(rs, "ALLOW_ASSIGN_DUE_DATE"));
          tmpWorkflowStep.setAllowAssignPriority(getResultSetString(rs, "ALLOW_ASSIGN_PRIORITY"));
          tmpWorkflowStep.setAllowStop(getResultSetString(rs, "ALLOW_STOP"));
          tmpWorkflowStep.setOnCompleteAckMethod(getResultSetString(rs, "ON_COMPLETE_ACK_METHOD"));
          tmpWorkflowStep.setOnNoResponseFor(getResultSetInteger(rs, "ON_NO_RESPONSE_FOR"));
          tmpWorkflowStep.setOnNoResponseCalendarID(getResultSetString(rs, "ON_NO_RESPONSE_CALENDAR_ID"));
          tmpWorkflowStep.setOnNoResponseAfter(getResultSetTimestamp(rs, "ON_NO_RESPONSE_AFTER"));
          tmpWorkflowStep.setOnNoResponseAction(getResultSetString(rs, "ON_NO_RESPONSE_ACTION"));
          tmpWorkflowStep.setOnNoResponseNextStepID(getResultSetInteger(rs, "ON_NO_RESPONSE_NEXT_STEP_ID"));
          tmpWorkflowStep.setOnNoResponseAckMethod(getResultSetString(rs, "ON_NO_RESPONSE_ACK_METHOD"));
          tmpWorkflowStep.setFilterBy(getResultSetInteger(rs, "FILTER_BY"));
          tmpWorkflowStep.setRecordStatus(getResultSetString(rs, "RECORD_STATUS"));
          tmpWorkflowStep.setUpdateCount(getResultSetInteger(rs, "UPDATE_COUNT"));
          tmpWorkflowStep.setCreatorID(getResultSetInteger(rs, "CREATOR_ID"));
          tmpWorkflowStep.setCreateDate(getResultSetTimestamp(rs, "CREATE_DATE"));
          tmpWorkflowStep.setUpdaterID(getResultSetInteger(rs, "UPDATER_ID"));
          tmpWorkflowStep.setUpdateDate(getResultSetTimestamp(rs, "UPDATE_DATE"));
          tmpWorkflowStep.setCreatorName(UserInfoFactory.getUserFullName(tmpWorkflowStep.getCreatorID()));
          tmpWorkflowStep.setUpdaterName(UserInfoFactory.getUserFullName(tmpWorkflowStep.getUpdaterID()));
          tmpWorkflowStep.setParentWorkflowStepType(getResultSetString(rs, "PARENT_TYPE"));
          result.add(tmpWorkflowStep);
        }
        return(result);
      } catch (ApplicationException appEx) {
        throw appEx;
      } catch (SQLException sqle) {
        log.error(sqle, sqle);
        throw new ApplicationException(ErrorConstant.DB_GENERAL_ERROR, sqle, sqle.toString());
      } catch (Exception e) {
        log.error(e, e);
        throw new ApplicationException(ErrorConstant.DB_SELECT_ERROR, e);
      } finally {
        try { rs.close(); } catch (Exception ignore) {} finally { rs = null; }
        try { preStat.close(); } catch (Exception ignore) {} finally { preStat = null; }
      }
    }
  }
  /**
   * Gets the direct distinct child step(s) of the given parent step
   * 
   * @param parentStepID The parent step id
   * @return List of distinct child workflow steps
   * @throws ApplicationException
   * @see com.dcivision.workflow.bean.WorkflowStep
   */
  public synchronized List getDistinctListByParentStepID(Integer parentStepID) throws ApplicationException {
    PreparedStatement preStat = null;
    ResultSet rs = null;
    StringBuffer sqlStat = new StringBuffer();
    List result = new ArrayList();
    
    synchronized(dbConn) {
      try {
        sqlStat.append("SELECT    DISTINCT A.ID, A.WORKFLOW_RECORD_ID, A.STEP_SEQ, A.STEP_NAME, A.ACTION_TYPE, A.ACTION_ID, A.DESCRIPTION, A.WORKFLOW_RULE, A.FORM_SECTION_ID, A.PRIORITY, A.ALLOW_ASSIGN_DUE_DATE, A.ALLOW_ASSIGN_PRIORITY, A.ALLOW_STOP, A.ON_COMPLETE_ACK_METHOD, A.FILTER_BY, A.ON_NO_RESPONSE_FOR, A.ON_NO_RESPONSE_CALENDAR_ID, A.ON_NO_RESPONSE_AFTER, A.ON_NO_RESPONSE_ACTION, A.ON_NO_RESPONSE_NEXT_STEP_ID, A.ON_NO_RESPONSE_ACK_METHOD, A.RECORD_STATUS, A.UPDATE_COUNT, A.CREATOR_ID, A.CREATE_DATE, A.UPDATER_ID, A.UPDATE_DATE ");
        sqlStat.append("FROM      WORKFLOW_STEP A, MTM_WF_STEP_WF_STEP B ");
        sqlStat.append("WHERE     A.RECORD_STATUS = ? AND B.RECORD_STATUS = ? AND A.ID = B.CHILD_STEP_ID AND B.PARENT_STEP_ID = ? ");
        sqlStat.append("ORDER BY  A.ID ASC ");
        preStat = dbConn.prepareStatement(sqlStat.toString(), ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
        this.setPrepareStatement(preStat, 1, GlobalConstant.RECORD_STATUS_ACTIVE);
        this.setPrepareStatement(preStat, 2, GlobalConstant.RECORD_STATUS_ACTIVE);
        this.setPrepareStatement(preStat, 3, parentStepID);
        rs = preStat.executeQuery();
        while (rs.next()) {
          WorkflowStep tmpWorkflowStep = new WorkflowStep();
          tmpWorkflowStep.setID(getResultSetInteger(rs, "ID"));
          tmpWorkflowStep.setWorkflowRecordID(getResultSetInteger(rs, "WORKFLOW_RECORD_ID"));
          tmpWorkflowStep.setStepSeq(getResultSetInteger(rs, "STEP_SEQ"));
          tmpWorkflowStep.setStepName(getResultSetString(rs, "STEP_NAME"));
          tmpWorkflowStep.setActionType(getResultSetString(rs, "ACTION_TYPE"));
          tmpWorkflowStep.setActionID(getResultSetInteger(rs, "ACTION_ID"));
          tmpWorkflowStep.setDescription(getResultSetString(rs, "DESCRIPTION"));
          tmpWorkflowStep.setWorkflowRule(getResultSetString(rs, "WORKFLOW_RULE"));
          tmpWorkflowStep.setSectionID(getResultSetString(rs, "FORM_SECTION_ID"));
          tmpWorkflowStep.setPriority(getResultSetString(rs, "PRIORITY"));
          tmpWorkflowStep.setAllowAssignDueDate(getResultSetString(rs, "ALLOW_ASSIGN_DUE_DATE"));
          tmpWorkflowStep.setAllowAssignPriority(getResultSetString(rs, "ALLOW_ASSIGN_PRIORITY"));
          tmpWorkflowStep.setAllowStop(getResultSetString(rs, "ALLOW_STOP"));
          tmpWorkflowStep.setOnCompleteAckMethod(getResultSetString(rs, "ON_COMPLETE_ACK_METHOD"));
          tmpWorkflowStep.setOnNoResponseFor(getResultSetInteger(rs, "ON_NO_RESPONSE_FOR"));
          tmpWorkflowStep.setOnNoResponseCalendarID(getResultSetString(rs, "ON_NO_RESPONSE_CALENDAR_ID"));
          tmpWorkflowStep.setOnNoResponseAfter(getResultSetTimestamp(rs, "ON_NO_RESPONSE_AFTER"));
          tmpWorkflowStep.setOnNoResponseAction(getResultSetString(rs, "ON_NO_RESPONSE_ACTION"));
          tmpWorkflowStep.setOnNoResponseNextStepID(getResultSetInteger(rs, "ON_NO_RESPONSE_NEXT_STEP_ID"));
          tmpWorkflowStep.setOnNoResponseAckMethod(getResultSetString(rs, "ON_NO_RESPONSE_ACK_METHOD"));
          tmpWorkflowStep.setFilterBy(getResultSetInteger(rs, "FILTER_BY"));
          tmpWorkflowStep.setRecordStatus(getResultSetString(rs, "RECORD_STATUS"));
          tmpWorkflowStep.setUpdateCount(getResultSetInteger(rs, "UPDATE_COUNT"));
          tmpWorkflowStep.setCreatorID(getResultSetInteger(rs, "CREATOR_ID"));
          tmpWorkflowStep.setCreateDate(getResultSetTimestamp(rs, "CREATE_DATE"));
          tmpWorkflowStep.setUpdaterID(getResultSetInteger(rs, "UPDATER_ID"));
          tmpWorkflowStep.setUpdateDate(getResultSetTimestamp(rs, "UPDATE_DATE"));
          tmpWorkflowStep.setCreatorName(UserInfoFactory.getUserFullName(tmpWorkflowStep.getCreatorID()));
          tmpWorkflowStep.setUpdaterName(UserInfoFactory.getUserFullName(tmpWorkflowStep.getUpdaterID()));
          
          result.add(tmpWorkflowStep);
        }
        return(result);
      } catch (ApplicationException appEx) {
        throw appEx;
      } catch (SQLException sqle) {
        log.error(sqle, sqle);
        throw new ApplicationException(ErrorConstant.DB_GENERAL_ERROR, sqle, sqle.toString());
      } catch (Exception e) {
        log.error(e, e);
        throw new ApplicationException(ErrorConstant.DB_SELECT_ERROR, e);
      } finally {
        try { rs.close(); } catch (Exception ignore) {} finally { rs = null; }
        try { preStat.close(); } catch (Exception ignore) {} finally { preStat = null; }
      }
    }
  }
  
  
  /**
   * Gets the direct parent step(s) of a given child step
   * 
   * @param childStepID The child step id
   * @return List of parent workflow steps
   * @throws ApplicationException
   * @see com.dcivision.workflow.bean.WorkflowStep
   */
  public synchronized List getListByChildStepID(Integer childStepID) throws ApplicationException {
    PreparedStatement preStat = null;
    ResultSet rs = null;
    StringBuffer sqlStat = new StringBuffer();
    List result = new ArrayList();
    List parentActionType = new ArrayList();
    synchronized(dbConn) {
      try {
        sqlStat.append("SELECT    A.ID, A.WORKFLOW_RECORD_ID, A.STEP_SEQ, A.STEP_NAME, A.ACTION_TYPE, A.ACTION_ID, A.DESCRIPTION, A.WORKFLOW_RULE, A.FORM_SECTION_ID, A.PRIORITY, A.ALLOW_ASSIGN_DUE_DATE, A.ALLOW_STOP, A.ON_COMPLETE_ACK_METHOD, A.FILTER_BY, A.ON_NO_RESPONSE_FOR, A.ON_NO_RESPONSE_CALENDAR_ID, A.ON_NO_RESPONSE_AFTER, A.ON_NO_RESPONSE_ACTION, A.ON_NO_RESPONSE_NEXT_STEP_ID, A.ON_NO_RESPONSE_ACK_METHOD, A.RECORD_STATUS, A.UPDATE_COUNT, A.CREATOR_ID, A.CREATE_DATE, A.UPDATER_ID, A.UPDATE_DATE, B.PARENT_TYPE ");
        sqlStat.append("FROM      WORKFLOW_STEP A, MTM_WF_STEP_WF_STEP B ");
        sqlStat.append("WHERE     A.RECORD_STATUS = ? AND B.RECORD_STATUS = ? AND A.ID = B.PARENT_STEP_ID AND B.CHILD_STEP_ID = ? ");
        
        preStat = dbConn.prepareStatement(sqlStat.toString(), ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
        this.setPrepareStatement(preStat, 1, GlobalConstant.RECORD_STATUS_ACTIVE);
        this.setPrepareStatement(preStat, 2, GlobalConstant.RECORD_STATUS_ACTIVE);
        this.setPrepareStatement(preStat, 3, childStepID);
        rs = preStat.executeQuery();
        
        while (rs.next()) {
          WorkflowStep tmpWorkflowStep = new WorkflowStep();
          tmpWorkflowStep.setID(getResultSetInteger(rs, "ID"));
          tmpWorkflowStep.setWorkflowRecordID(getResultSetInteger(rs, "WORKFLOW_RECORD_ID"));
          tmpWorkflowStep.setStepSeq(getResultSetInteger(rs, "STEP_SEQ"));
          tmpWorkflowStep.setStepName(getResultSetString(rs, "STEP_NAME"));
          tmpWorkflowStep.setActionType(getResultSetString(rs, "ACTION_TYPE"));
          tmpWorkflowStep.setActionID(getResultSetInteger(rs, "ACTION_ID"));
          tmpWorkflowStep.setDescription(getResultSetString(rs, "DESCRIPTION"));
          tmpWorkflowStep.setWorkflowRule(getResultSetString(rs, "WORKFLOW_RULE"));
          tmpWorkflowStep.setSectionID(getResultSetString(rs, "FORM_SECTION_ID"));
          tmpWorkflowStep.setPriority(getResultSetString(rs, "PRIORITY"));
          tmpWorkflowStep.setAllowAssignDueDate(getResultSetString(rs, "ALLOW_ASSIGN_DUE_DATE"));
          tmpWorkflowStep.setAllowStop(getResultSetString(rs, "ALLOW_STOP"));
          tmpWorkflowStep.setOnCompleteAckMethod(getResultSetString(rs, "ON_COMPLETE_ACK_METHOD"));
          tmpWorkflowStep.setOnNoResponseFor(getResultSetInteger(rs, "ON_NO_RESPONSE_FOR"));
          tmpWorkflowStep.setOnNoResponseCalendarID(getResultSetString(rs, "ON_NO_RESPONSE_CALENDAR_ID"));
          tmpWorkflowStep.setOnNoResponseAfter(getResultSetTimestamp(rs, "ON_NO_RESPONSE_AFTER"));
          tmpWorkflowStep.setOnNoResponseAction(getResultSetString(rs, "ON_NO_RESPONSE_ACTION"));
          tmpWorkflowStep.setOnNoResponseNextStepID(getResultSetInteger(rs, "ON_NO_RESPONSE_NEXT_STEP_ID"));
          tmpWorkflowStep.setOnNoResponseAckMethod(getResultSetString(rs, "ON_NO_RESPONSE_ACK_METHOD"));
          tmpWorkflowStep.setFilterBy(getResultSetInteger(rs, "FILTER_BY"));
          tmpWorkflowStep.setRecordStatus(getResultSetString(rs, "RECORD_STATUS"));
          tmpWorkflowStep.setUpdateCount(getResultSetInteger(rs, "UPDATE_COUNT"));
          tmpWorkflowStep.setCreatorID(getResultSetInteger(rs, "CREATOR_ID"));
          tmpWorkflowStep.setCreateDate(getResultSetTimestamp(rs, "CREATE_DATE"));
          tmpWorkflowStep.setUpdaterID(getResultSetInteger(rs, "UPDATER_ID"));
          tmpWorkflowStep.setUpdateDate(getResultSetTimestamp(rs, "UPDATE_DATE"));
          tmpWorkflowStep.setCreatorName(UserInfoFactory.getUserFullName(tmpWorkflowStep.getCreatorID()));
          tmpWorkflowStep.setUpdaterName(UserInfoFactory.getUserFullName(tmpWorkflowStep.getUpdaterID()));
          tmpWorkflowStep.setParentWorkflowStepType(getResultSetString(rs, "PARENT_TYPE"));
          parentActionType.add(tmpWorkflowStep);
        }
        return(parentActionType);
      } catch (ApplicationException appEx) {
        throw appEx;
      } catch (SQLException sqle) {
        log.error(sqle, sqle);
        throw new ApplicationException(ErrorConstant.DB_GENERAL_ERROR, sqle, sqle.toString());
      } catch (Exception e) {
        log.error(e, e);
        throw new ApplicationException(ErrorConstant.DB_SELECT_ERROR, e);
      } finally {
        try { rs.close(); } catch (Exception ignore) {} finally { rs = null; }
        try { preStat.close(); } catch (Exception ignore) {} finally { preStat = null; }
      }
    }
  }
  /**
   * Gets the pre-assigned owners of the given step
   * @param StepID The workflow step ID
   * @return List of user record id that is pre-assigned as owner of this step
   * @throws ApplicationException
   * @see com.dcivision.user.bean.UserRecord
   * @deprecated
   */
  public synchronized List getUserListByStepID(Integer StepID) throws ApplicationException {
    PreparedStatement preStat = null;
    ResultSet rs = null;
    StringBuffer sqlStat = new StringBuffer();
    List result = new ArrayList();
    synchronized(dbConn) {
      try {
        sqlStat.append("SELECT  USER_RECORD_ID ");
        sqlStat.append("FROM MTM_WF_STEP_USER_ACTOR A,MTM_USER_RECORD_USER_GROUP B ");
        sqlStat.append("WHERE ACTOR_ID=USER_GROUP_ID AND ACTOR_TYPE='G' AND A.RECORD_STATUS = ? AND B.RECORD_STATUS = ? AND WORKFLOW_STEP_ID = ? ");
        sqlStat.append("UNION ");
        sqlStat.append("SELECT  USER_RECORD_ID ");
        sqlStat.append("FROM MTM_WF_STEP_USER_ACTOR A,MTM_USER_RECORD_USER_ROLE B ");
        sqlStat.append("WHERE ACTOR_ID=USER_ROLE_ID AND ACTOR_TYPE='R' AND A.RECORD_STATUS = ? AND B.RECORD_STATUS = ? AND WORKFLOW_STEP_ID = ? ");
        sqlStat.append("UNION ");
        sqlStat.append("SELECT  B.ID  USER_RECORD_ID ");
        sqlStat.append("FROM MTM_WF_STEP_USER_ACTOR A,USER_RECORD B ");
        sqlStat.append("WHERE ACTOR_ID=B.ID AND ACTOR_TYPE<>'R' AND ACTOR_TYPE<>'G' AND A.RECORD_STATUS = ? AND B.RECORD_STATUS = ?  AND WORKFLOW_STEP_ID = ? ");
        
        preStat = dbConn.prepareStatement(sqlStat.toString(), ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
        this.setPrepareStatement(preStat, 1, GlobalConstant.RECORD_STATUS_ACTIVE);
        this.setPrepareStatement(preStat, 2, GlobalConstant.RECORD_STATUS_ACTIVE);
        this.setPrepareStatement(preStat, 3, StepID);
        this.setPrepareStatement(preStat, 4, GlobalConstant.RECORD_STATUS_ACTIVE);
        this.setPrepareStatement(preStat, 5, GlobalConstant.RECORD_STATUS_ACTIVE);
        this.setPrepareStatement(preStat, 6, StepID);
        this.setPrepareStatement(preStat, 7, GlobalConstant.RECORD_STATUS_ACTIVE);
        this.setPrepareStatement(preStat, 8, GlobalConstant.RECORD_STATUS_ACTIVE);
        this.setPrepareStatement(preStat, 9, StepID);
        
        rs = preStat.executeQuery();
        while (rs.next()) {
          result.add(getResultSetInteger(rs, "USER_RECORD_ID"));
        }
        return(result);
      } catch (ApplicationException appEx) {
        throw appEx;
      } catch (SQLException sqle) {
        log.error(sqle, sqle);
        throw new ApplicationException(ErrorConstant.DB_GENERAL_ERROR, sqle, sqle.toString());
      } catch (Exception e) {
        log.error(e, e);
        throw new ApplicationException(ErrorConstant.DB_SELECT_ERROR, e);
      } finally {
        try { rs.close(); } catch (Exception ignore) {} finally { rs = null; }
        try { preStat.close(); } catch (Exception ignore) {} finally { preStat = null; }
      }
    }
  }
  /**
   * Gets all the workflow steps of the given workflow record, except those ID specified in the list
   * 
   * @param workflowRecordID The workflow record ID
   * @param stepList List of workflow step ID (Integer) to be excluded
   * @return List of WorkflowStep objects
   * @throws ApplicationException
   * @see com.dcivision.workflow.bean.WorkflowStep
   * @see com.dcivision.workflow.bean.WorkflowRecord
   */
  public synchronized List getListByWorkflowRecordIDAndExcludeSteps(Integer workflowRecordID, List stepList) throws ApplicationException {
    PreparedStatement preStat = null;
    ResultSet rs = null;
    StringBuffer sqlStat = new StringBuffer();
    List result = new ArrayList();
    
    synchronized(dbConn) {
      try {
        sqlStat.append("SELECT    A.ID, A.WORKFLOW_RECORD_ID, A.STEP_SEQ, A.STEP_NAME, A.ACTION_TYPE, A.ACTION_ID, A.DESCRIPTION, A.WORKFLOW_RULE, A.FORM_SECTION_ID, A.PRIORITY, A.ALLOW_ASSIGN_DUE_DATE, A.ALLOW_ASSIGN_PRIORITY, A.ALLOW_STOP, A.ON_COMPLETE_ACK_METHOD, A.FILTER_BY, A.ON_NO_RESPONSE_FOR, A.ON_NO_RESPONSE_CALENDAR_ID, A.ON_NO_RESPONSE_AFTER, A.ON_NO_RESPONSE_ACTION, A.ON_NO_RESPONSE_NEXT_STEP_ID, A.ON_NO_RESPONSE_ACK_METHOD, A.RECORD_STATUS, A.UPDATE_COUNT, A.CREATOR_ID, A.CREATE_DATE, A.UPDATER_ID, A.UPDATE_DATE ");
        sqlStat.append("FROM      WORKFLOW_STEP A ");
        sqlStat.append("WHERE     A.RECORD_STATUS = ? AND A.WORKFLOW_RECORD_ID = ? ");
        for (int i=0; i<stepList.size(); i++) {
          sqlStat.append("        AND A.ID != ? ");
        }
        sqlStat.append("ORDER BY  A.ID ASC ");
        preStat = dbConn.prepareStatement(sqlStat.toString(), ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
        this.setPrepareStatement(preStat, 1, GlobalConstant.RECORD_STATUS_ACTIVE);
        this.setPrepareStatement(preStat, 2, workflowRecordID);
        for (int i=0; i<stepList.size(); i++) {
          this.setPrepareStatement(preStat, i+3, (Integer)stepList.get(i));
        }
        rs = preStat.executeQuery();
        while (rs.next()) {
          WorkflowStep tmpWorkflowStep = new WorkflowStep();
          tmpWorkflowStep.setID(getResultSetInteger(rs, "ID"));
          tmpWorkflowStep.setWorkflowRecordID(getResultSetInteger(rs, "WORKFLOW_RECORD_ID"));
          tmpWorkflowStep.setStepSeq(getResultSetInteger(rs, "STEP_SEQ"));
          tmpWorkflowStep.setStepName(getResultSetString(rs, "STEP_NAME"));
          tmpWorkflowStep.setActionType(getResultSetString(rs, "ACTION_TYPE"));
          tmpWorkflowStep.setActionID(getResultSetInteger(rs, "ACTION_ID"));
          tmpWorkflowStep.setDescription(getResultSetString(rs, "DESCRIPTION"));
          tmpWorkflowStep.setWorkflowRule(getResultSetString(rs, "WORKFLOW_RULE"));
          tmpWorkflowStep.setSectionID(getResultSetString(rs, "FORM_SECTION_ID"));
          tmpWorkflowStep.setPriority(getResultSetString(rs, "PRIORITY"));
          tmpWorkflowStep.setAllowAssignDueDate(getResultSetString(rs, "ALLOW_ASSIGN_DUE_DATE"));
          tmpWorkflowStep.setAllowAssignPriority(getResultSetString(rs, "ALLOW_ASSIGN_PRIORITY"));
          tmpWorkflowStep.setAllowStop(getResultSetString(rs, "ALLOW_STOP"));
          tmpWorkflowStep.setOnCompleteAckMethod(getResultSetString(rs, "ON_COMPLETE_ACK_METHOD"));
          tmpWorkflowStep.setOnNoResponseFor(getResultSetInteger(rs, "ON_NO_RESPONSE_FOR"));
          tmpWorkflowStep.setOnNoResponseCalendarID(getResultSetString(rs, "ON_NO_RESPONSE_CALENDAR_ID"));
          tmpWorkflowStep.setOnNoResponseAfter(getResultSetTimestamp(rs, "ON_NO_RESPONSE_AFTER"));
          tmpWorkflowStep.setOnNoResponseAction(getResultSetString(rs, "ON_NO_RESPONSE_ACTION"));
          tmpWorkflowStep.setOnNoResponseNextStepID(getResultSetInteger(rs, "ON_NO_RESPONSE_NEXT_STEP_ID"));
          tmpWorkflowStep.setOnNoResponseAckMethod(getResultSetString(rs, "ON_NO_RESPONSE_ACK_METHOD"));
          tmpWorkflowStep.setFilterBy(getResultSetInteger(rs, "FILTER_BY"));
          tmpWorkflowStep.setRecordStatus(getResultSetString(rs, "RECORD_STATUS"));
          tmpWorkflowStep.setUpdateCount(getResultSetInteger(rs, "UPDATE_COUNT"));
          tmpWorkflowStep.setCreatorID(getResultSetInteger(rs, "CREATOR_ID"));
          tmpWorkflowStep.setCreateDate(getResultSetTimestamp(rs, "CREATE_DATE"));
          tmpWorkflowStep.setUpdaterID(getResultSetInteger(rs, "UPDATER_ID"));
          tmpWorkflowStep.setUpdateDate(getResultSetTimestamp(rs, "UPDATE_DATE"));
          tmpWorkflowStep.setCreatorName(UserInfoFactory.getUserFullName(tmpWorkflowStep.getCreatorID()));
          tmpWorkflowStep.setUpdaterName(UserInfoFactory.getUserFullName(tmpWorkflowStep.getUpdaterID()));
          result.add(tmpWorkflowStep);
        }
        
        WorkflowActionDAObject actionDAO = new WorkflowActionDAObject(sessionContainer, dbConn);
        WorkflowStepRouteDAObject wfStepRouteDAO = new WorkflowStepRouteDAObject(sessionContainer, dbConn);
        WorkflowStepFormEquatDAObject formEquatDAO = new WorkflowStepFormEquatDAObject(sessionContainer, dbConn);
        for (int i=0; i<result.size(); i++) {
          WorkflowStep tmpWorkflowStep = (WorkflowStep) result.get(i);
          //Auto Task
          if (!Utility.isEmpty(tmpWorkflowStep.getActionID()) ) {
            WorkflowAction tmpAction = (WorkflowAction) actionDAO.getObjectByID(tmpWorkflowStep.getActionID());
            tmpWorkflowStep.setWorkflowAction(tmpAction);
             WorkflowProcessor processor = AutomaticTaskFactory.getInstance(tmpAction);
            tmpWorkflowStep.setNumOfRoutine(processor.getNumOfRoutine());
          }
          //Decision Point Task
          if(WorkflowStep.ACTION_TYPE_USER_DEFINED_TASK.equals(tmpWorkflowStep.getActionType())){
            List routeList = wfStepRouteDAO.getListByWorkflowStepID(tmpWorkflowStep.getID());
            String routeListValue = "";
            for(int j=0; j<routeList.size(); j++){
              WorkflowStepRoute route = (WorkflowStepRoute)routeList.get(j);
              routeListValue += route.getRouteName() + "|";
            }
            tmpWorkflowStep.setNumOfRoutine(routeList.size());
            tmpWorkflowStep.setRouteListValue(routeListValue);
          }
          //Form Value Driven Task
          if(WorkflowStep.ACTION_TYPE_FORM_DRIVEN.equals(tmpWorkflowStep.getActionType())){
            List routeList = formEquatDAO.getListByWorkflowStepID(tmpWorkflowStep.getID());
            String routeListValue = "";
            for(int j=0; j<routeList.size(); j++){
              WorkflowStepFormEquat route = (WorkflowStepFormEquat)routeList.get(j);
              routeListValue += route.getRouteName() + "|";
            }
            tmpWorkflowStep.setNumOfRoutine(routeList.size());
            tmpWorkflowStep.setRouteListValue(routeListValue);
          }
          
          result.set(i, tmpWorkflowStep);
        }
        return(result);
      } catch (ApplicationException appEx) {
        throw appEx;
      } catch (SQLException sqle) {
        log.error(sqle, sqle);
        throw new ApplicationException(ErrorConstant.DB_GENERAL_ERROR, sqle, sqle.toString());
      } catch (Exception e) {
        log.error(e, e);
        throw new ApplicationException(ErrorConstant.DB_SELECT_ERROR, e);
      } finally {
        try { rs.close(); } catch (Exception ignore) {} finally { rs = null; }
        try { preStat.close(); } catch (Exception ignore) {} finally { preStat = null; }
      }
    }
  }
  /**
   * Gets the next step sequence number of the given workflow record
   * 
   * @param workflowRecordID The workflow record ID
   * @return The next sequence number 
   * @throws ApplicationException
   * @see com.dcivision.workflow.bean.WorkflowRecord
   */
  public synchronized Integer getNextStepSeqNumber(Integer workflowRecordID) throws ApplicationException {
    PreparedStatement preStat = null;
    ResultSet rs = null;
    StringBuffer sqlStat = new StringBuffer();
    Integer result = null;
    
    synchronized(dbConn) {
      try {
        sqlStat.append("SELECT MAX(A.STEP_SEQ) + 1 AS NEXT_STEP_SEQ ");
        sqlStat.append("FROM   WORKFLOW_STEP A ");
        sqlStat.append("WHERE  A.RECORD_STATUS = ? AND A.WORKFLOW_RECORD_ID = ? AND A.STEP_SEQ <> ? ");
        preStat = dbConn.prepareStatement(sqlStat.toString(), ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
        this.setPrepareStatement(preStat, 1, GlobalConstant.RECORD_STATUS_ACTIVE);
        this.setPrepareStatement(preStat, 2, workflowRecordID);
        this.setPrepareStatement(preStat, 3, WorkflowStep.END_STEP_SEQ_NO);
        rs = preStat.executeQuery();
        if  (rs.next()) {
          result = this.getResultSetInteger(rs, "NEXT_STEP_SEQ");
        }
        return(result);
      } catch (ApplicationException appEx) {
        throw appEx;
      } catch (SQLException sqle) {
        log.error(sqle, sqle);
        throw new ApplicationException(ErrorConstant.DB_GENERAL_ERROR, sqle, sqle.toString());
      } catch (Exception e) {
        log.error(e, e);
        throw new ApplicationException(ErrorConstant.DB_SELECT_ERROR, e);
      } finally {
        try { rs.close(); } catch (Exception ignore) {} finally { rs = null; }
        try { preStat.close(); } catch (Exception ignore) {} finally { preStat = null; }
      }
    }
  }
  /**
   * Gets the last step "End Step" of the given workflow record
   * 
   * @param workflowRecordID The workflow record ID
   * @return The END workflow step
   * @throws ApplicationException
   * @see com.dcivision.workflow.bean.WorkflowStep
   * @see com.dcivision.workflow.bean.WorkflowRecord
   */
  public synchronized AbstractBaseObject getEndStepByWorkflowRecordID(Integer workflowRecordID) throws ApplicationException {
  	PreparedStatement preStat = null;
    ResultSet rs = null;
    StringBuffer sqlStat = new StringBuffer();
    
    synchronized(dbConn) {
      try {
        sqlStat.append("SELECT A.ID, A.WORKFLOW_RECORD_ID, A.STEP_SEQ, A.STEP_NAME, A.ACTION_TYPE, A.ACTION_ID, A.DESCRIPTION, A.WORKFLOW_RULE, A.FORM_SECTION_ID, A.PRIORITY, A.ALLOW_ASSIGN_DUE_DATE, A.ALLOW_ASSIGN_PRIORITY, A.ALLOW_STOP, A.ON_COMPLETE_ACK_METHOD, A.ON_NO_RESPONSE_FOR, A.ON_NO_RESPONSE_CALENDAR_ID, A.ON_NO_RESPONSE_AFTER, A.ON_NO_RESPONSE_ACTION, A.ON_NO_RESPONSE_NEXT_STEP_ID, A.ON_NO_RESPONSE_ACK_METHOD, A.FILTER_BY, A.RECORD_STATUS, A.UPDATE_COUNT, A.CREATOR_ID, A.CREATE_DATE, A.UPDATER_ID, A.UPDATE_DATE ");
        sqlStat.append("FROM   WORKFLOW_STEP A ");
        sqlStat.append("WHERE  A.WORKFLOW_RECORD_ID = ? AND A.STEP_SEQ = ? ");
        preStat = dbConn.prepareStatement(sqlStat.toString(), ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
        this.setPrepareStatement(preStat, 1, workflowRecordID);
        this.setPrepareStatement(preStat, 2, WorkflowStep.END_STEP_SEQ_NO);
        rs = preStat.executeQuery();
        if (rs.next()) {
          WorkflowStep tmpWorkflowStep = new WorkflowStep();
          tmpWorkflowStep.setID(getResultSetInteger(rs, "ID"));
          tmpWorkflowStep.setWorkflowRecordID(getResultSetInteger(rs, "WORKFLOW_RECORD_ID"));
          tmpWorkflowStep.setStepSeq(getResultSetInteger(rs, "STEP_SEQ"));
          tmpWorkflowStep.setStepName(getResultSetString(rs, "STEP_NAME"));
          tmpWorkflowStep.setActionType(getResultSetString(rs, "ACTION_TYPE"));
          tmpWorkflowStep.setActionID(getResultSetInteger(rs, "ACTION_ID"));
          tmpWorkflowStep.setDescription(getResultSetString(rs, "DESCRIPTION"));
          tmpWorkflowStep.setWorkflowRule(getResultSetString(rs, "WORKFLOW_RULE"));
          tmpWorkflowStep.setSectionID(getResultSetString(rs, "FORM_SECTION_ID"));
          tmpWorkflowStep.setPriority(getResultSetString(rs, "PRIORITY"));
          tmpWorkflowStep.setAllowAssignDueDate(getResultSetString(rs, "ALLOW_ASSIGN_DUE_DATE"));
          tmpWorkflowStep.setAllowAssignPriority(getResultSetString(rs, "ALLOW_ASSIGN_PRIORITY"));
          tmpWorkflowStep.setAllowStop(getResultSetString(rs, "ALLOW_STOP"));
          tmpWorkflowStep.setOnCompleteAckMethod(getResultSetString(rs, "ON_COMPLETE_ACK_METHOD"));
          tmpWorkflowStep.setOnNoResponseFor(getResultSetInteger(rs, "ON_NO_RESPONSE_FOR"));
          tmpWorkflowStep.setOnNoResponseCalendarID(getResultSetString(rs, "ON_NO_RESPONSE_CALENDAR_ID"));
          tmpWorkflowStep.setOnNoResponseAfter(getResultSetTimestamp(rs, "ON_NO_RESPONSE_AFTER"));
          tmpWorkflowStep.setOnNoResponseAction(getResultSetString(rs, "ON_NO_RESPONSE_ACTION"));
          tmpWorkflowStep.setOnNoResponseNextStepID(getResultSetInteger(rs, "ON_NO_RESPONSE_NEXT_STEP_ID"));
          tmpWorkflowStep.setOnNoResponseAckMethod(getResultSetString(rs, "ON_NO_RESPONSE_ACK_METHOD"));
          tmpWorkflowStep.setFilterBy(getResultSetInteger(rs, "FILTER_BY"));
          tmpWorkflowStep.setRecordStatus(getResultSetString(rs, "RECORD_STATUS"));
          tmpWorkflowStep.setUpdateCount(getResultSetInteger(rs, "UPDATE_COUNT"));
          tmpWorkflowStep.setCreatorID(getResultSetInteger(rs, "CREATOR_ID"));
          tmpWorkflowStep.setCreateDate(getResultSetTimestamp(rs, "CREATE_DATE"));
          tmpWorkflowStep.setUpdaterID(getResultSetInteger(rs, "UPDATER_ID"));
          tmpWorkflowStep.setUpdateDate(getResultSetTimestamp(rs, "UPDATE_DATE"));
          tmpWorkflowStep.setCreatorName(UserInfoFactory.getUserFullName(tmpWorkflowStep.getCreatorID()));
          tmpWorkflowStep.setUpdaterName(UserInfoFactory.getUserFullName(tmpWorkflowStep.getUpdaterID()));
          return(tmpWorkflowStep);
        } else {
          return null;
        }
      } catch (ApplicationException appEx) {
        throw appEx;
      } catch (SQLException sqle) {
        log.error(sqle, sqle);
        throw new ApplicationException(ErrorConstant.DB_GENERAL_ERROR, sqle, sqle.toString());
      } catch (Exception e) {
        log.error(e, e);
        throw new ApplicationException(ErrorConstant.DB_SELECT_ERROR, e);
      } finally {
        try { rs.close(); } catch (Exception ignore) {} finally { rs = null; }
        try { preStat.close(); } catch (Exception ignore) {} finally { preStat = null; }
      }
    }
  }
  /**
   * Gets the workflow step ID of the given workflow record and sequence number
   * 
   * @param workflowRecordID The workflow record ID
   * @param StepSeq The step sequence number
   * @return The workflow step ID
   * @throws ApplicationException
   * @see com.dcivision.workflow.bean.WorkflowStep
   * @see com.dcivision.workflow.bean.WorkflowRecord
   */
  public synchronized Integer getStepIDByWorkflowIDAndStepSeq(Integer workflowRecordID,Integer StepSeq) throws ApplicationException {
    PreparedStatement preStat = null;
    ResultSet rs = null;
    StringBuffer sqlStat = new StringBuffer();
    Integer result = null;
    
    synchronized(dbConn) {
      try {
        sqlStat.append("SELECT A.ID ");
        sqlStat.append("FROM   WORKFLOW_STEP A ");
        sqlStat.append("WHERE  A.RECORD_STATUS = ? AND A.WORKFLOW_RECORD_ID = ? AND A.STEP_SEQ = ? ");
        preStat = dbConn.prepareStatement(sqlStat.toString(), ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
        this.setPrepareStatement(preStat, 1, GlobalConstant.RECORD_STATUS_ACTIVE);
        this.setPrepareStatement(preStat, 2, workflowRecordID);
        this.setPrepareStatement(preStat, 3, StepSeq);
        rs = preStat.executeQuery();
        if  (rs.next()) {
          result = this.getResultSetInteger(rs, "ID");
        }
        return(result);
      } catch (ApplicationException appEx) {
        throw appEx;
      } catch (SQLException sqle) {
        log.error(sqle, sqle);
        throw new ApplicationException(ErrorConstant.DB_GENERAL_ERROR, sqle, sqle.toString());
      } catch (Exception e) {
        log.error(e, e);
        throw new ApplicationException(ErrorConstant.DB_SELECT_ERROR, e);
      } finally {
        try { rs.close(); } catch (Exception ignore) {} finally { rs = null; }
        try { preStat.close(); } catch (Exception ignore) {} finally { preStat = null; }
      }
    }
  }
  /**
   * Updates the step type of the given workflow step 
   * 
   * @param ID The workflow step ID
   * @param stepType The step type to be updated to
   * @return True if the update is succeeded
   * @throws ApplicationException
   * @see com.dcivision.bean.WorkflowStep#ACTION_TYPE_SUBMIT
   * @see com.dcivision.bean.WorkflowStep#ACTION_TYPE_APPROVAL
   * @see com.dcivision.bean.WorkflowStep#ACTION_TYPE_BOOLEAN
   * @see com.dcivision.bean.WorkflowStep#ACTION_TYPE_TASK_TO_DO
   * @see com.dcivision.bean.WorkflowStep#ACTION_TYPE_SYSTEM_AUTO
   * @see com.dcivision.bean.WorkflowStep#ACTION_TYPE_FORM_DRIVEN
   * @see com.dcivision.bean.WorkflowStep#ACTION_TYPE_END
   * @see com.dcivision.bean.WorkflowStep#ACTION_TYPE_USER_DEFINED_TASK
   */
  //update actionType by ID
  public synchronized boolean updateStepTypeByID(Integer ID, String stepType) throws ApplicationException {
    PreparedStatement preStat = null;
    StringBuffer sqlStat = new StringBuffer();
    
    synchronized(dbConn) {
      try {
        int updateCnt = 0;
        Timestamp currTime = Utility.getCurrentTimestamp();
        sqlStat.append("UPDATE WORKFLOW_STEP ");
        sqlStat.append("SET  ACTION_TYPE=? ");
        sqlStat.append("WHERE  ID=? ");
        sqlStat.append("AND ACTION_TYPE <> ? ");
        sqlStat.append("AND ACTION_TYPE <> ? ");
        sqlStat.append("AND ACTION_TYPE <> ? ");
        preStat = dbConn.prepareStatement(sqlStat.toString(), ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
        setPrepareStatement(preStat, 1, stepType);
        setPrepareStatement(preStat, 2, ID);
        setPrepareStatement(preStat, 3, WorkflowStep.ACTION_TYPE_SUBMIT);
        setPrepareStatement(preStat, 4, WorkflowStep.ACTION_TYPE_END);
        setPrepareStatement(preStat, 5, WorkflowStep.ACTION_TYPE_SYSTEM_AUTO);
        
        updateCnt = preStat.executeUpdate();
        if (updateCnt < 0) {
          throw new ApplicationException(ErrorConstant.DB_CONCURRENT_ERROR);
        } else {
          return true;
        }
      } catch (ApplicationException appEx) {
        throw appEx;
      } catch (SQLException sqle) {
        log.error(sqle, sqle);
        throw new ApplicationException(ErrorConstant.DB_GENERAL_ERROR, sqle, sqle.toString());
      } catch (Exception e) {
        log.error(e, e);
        throw new ApplicationException(ErrorConstant.DB_UPDATE_ERROR, e);
      } finally {
        try { preStat.close(); } catch (Exception ignore) {} finally { preStat = null; }
      }
    }
  }
  /**
   * Gets the workflow step object of the given workflow record and sequence number
   * 
   * @param workflowRecordID The workflow record ID
   * @param seqNo The step sequence number
   * @return The workflow step object
   * @throws ApplicationException
   * @see com.dcivision.workflow.bean.WorkflowStep
   * @see com.dcivision.workflow.bean.WorkflowRecord
   */
  public synchronized AbstractBaseObject getStepByWorkflowRecordIDAndSeqNo(Integer workflowRecordID,Integer seqNo) throws ApplicationException {
    PreparedStatement preStat = null;
    ResultSet rs = null;
    StringBuffer sqlStat = new StringBuffer();
    
    synchronized(dbConn) {
      try {
        sqlStat.append("SELECT A.ID, A.WORKFLOW_RECORD_ID, A.STEP_SEQ, A.STEP_NAME, A.ACTION_TYPE, A.ACTION_ID, A.DESCRIPTION, A.WORKFLOW_RULE, A.FORM_SECTION_ID, A.PRIORITY, A.ALLOW_ASSIGN_DUE_DATE, A.ALLOW_ASSIGN_PRIORITY, A.ALLOW_STOP, A.ON_COMPLETE_ACK_METHOD, A.ON_NO_RESPONSE_FOR, A.ON_NO_RESPONSE_CALENDAR_ID, A.ON_NO_RESPONSE_AFTER, A.ON_NO_RESPONSE_ACTION, A.ON_NO_RESPONSE_NEXT_STEP_ID, A.ON_NO_RESPONSE_ACK_METHOD, A.FILTER_BY, A.RECORD_STATUS, A.UPDATE_COUNT, A.CREATOR_ID, A.CREATE_DATE, A.UPDATER_ID, A.UPDATE_DATE ");
        sqlStat.append("FROM   WORKFLOW_STEP A ");
        sqlStat.append("WHERE  A.WORKFLOW_RECORD_ID = ? AND A.STEP_SEQ = ? AND A.RECORD_STATUS = ? ");
        preStat = dbConn.prepareStatement(sqlStat.toString(), ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
        this.setPrepareStatement(preStat, 1, workflowRecordID);
        this.setPrepareStatement(preStat, 2, seqNo);
        this.setPrepareStatement(preStat, 3, GlobalConstant.RECORD_STATUS_ACTIVE);
        rs = preStat.executeQuery();
        if (rs.next()) {
          WorkflowStep tmpWorkflowStep = new WorkflowStep();
          tmpWorkflowStep.setID(getResultSetInteger(rs, "ID"));
          tmpWorkflowStep.setWorkflowRecordID(getResultSetInteger(rs, "WORKFLOW_RECORD_ID"));
          tmpWorkflowStep.setStepSeq(getResultSetInteger(rs, "STEP_SEQ"));
          tmpWorkflowStep.setStepName(getResultSetString(rs, "STEP_NAME"));
          tmpWorkflowStep.setActionType(getResultSetString(rs, "ACTION_TYPE"));
          tmpWorkflowStep.setActionID(getResultSetInteger(rs, "ACTION_ID"));
          tmpWorkflowStep.setDescription(getResultSetString(rs, "DESCRIPTION"));
          tmpWorkflowStep.setWorkflowRule(getResultSetString(rs, "WORKFLOW_RULE"));
          tmpWorkflowStep.setSectionID(getResultSetString(rs, "FORM_SECTION_ID"));
          tmpWorkflowStep.setPriority(getResultSetString(rs, "PRIORITY"));
          tmpWorkflowStep.setAllowAssignDueDate(getResultSetString(rs, "ALLOW_ASSIGN_DUE_DATE"));
          tmpWorkflowStep.setAllowAssignPriority(getResultSetString(rs, "ALLOW_ASSIGN_PRIORITY"));
          tmpWorkflowStep.setAllowStop(getResultSetString(rs, "ALLOW_STOP"));
          tmpWorkflowStep.setOnCompleteAckMethod(getResultSetString(rs, "ON_COMPLETE_ACK_METHOD"));
          tmpWorkflowStep.setOnNoResponseFor(getResultSetInteger(rs, "ON_NO_RESPONSE_FOR"));
          tmpWorkflowStep.setOnNoResponseCalendarID(getResultSetString(rs, "ON_NO_RESPONSE_CALENDAR_ID"));
          tmpWorkflowStep.setOnNoResponseAfter(getResultSetTimestamp(rs, "ON_NO_RESPONSE_AFTER"));
          tmpWorkflowStep.setOnNoResponseAction(getResultSetString(rs, "ON_NO_RESPONSE_ACTION"));
          tmpWorkflowStep.setOnNoResponseNextStepID(getResultSetInteger(rs, "ON_NO_RESPONSE_NEXT_STEP_ID"));
          tmpWorkflowStep.setOnNoResponseAckMethod(getResultSetString(rs, "ON_NO_RESPONSE_ACK_METHOD"));
          tmpWorkflowStep.setFilterBy(getResultSetInteger(rs, "FILTER_BY"));
          tmpWorkflowStep.setRecordStatus(getResultSetString(rs, "RECORD_STATUS"));
          tmpWorkflowStep.setUpdateCount(getResultSetInteger(rs, "UPDATE_COUNT"));
          tmpWorkflowStep.setCreatorID(getResultSetInteger(rs, "CREATOR_ID"));
          tmpWorkflowStep.setCreateDate(getResultSetTimestamp(rs, "CREATE_DATE"));
          tmpWorkflowStep.setUpdaterID(getResultSetInteger(rs, "UPDATER_ID"));
          tmpWorkflowStep.setUpdateDate(getResultSetTimestamp(rs, "UPDATE_DATE"));
          tmpWorkflowStep.setCreatorName(UserInfoFactory.getUserFullName(tmpWorkflowStep.getCreatorID()));
          tmpWorkflowStep.setUpdaterName(UserInfoFactory.getUserFullName(tmpWorkflowStep.getUpdaterID()));
          return(tmpWorkflowStep);
        } else {
          return null;
        }
      } catch (ApplicationException appEx) {
        throw appEx;
      } catch (SQLException sqle) {
        log.error(sqle, sqle);
        throw new ApplicationException(ErrorConstant.DB_GENERAL_ERROR, sqle, sqle.toString());
      } catch (Exception e) {
        log.error(e, e);
        throw new ApplicationException(ErrorConstant.DB_SELECT_ERROR, e);
      } finally {
        try { rs.close(); } catch (Exception ignore) {} finally { rs = null; }
        try { preStat.close(); } catch (Exception ignore) {} finally { preStat = null; }
      }
    }
  }
  
  /**
   * Gets distinct parent step(s) of The given workflow child step
   * 
   * @param childStepID The workflow child step ID
   * @return list of WorkflowStep object
   * @throws ApplicationException
   */
  public synchronized List getDistinctParentStepListByChildStepID(Integer childStepID) throws ApplicationException {
    PreparedStatement preStat = null;
    ResultSet rs = null;
    StringBuffer sqlStat = new StringBuffer();
    List result = new ArrayList();
    List parentActionType = new ArrayList();
    synchronized(dbConn) {
      try {
        sqlStat.append(" SELECT  DISTINCT  A.ID, A.WORKFLOW_RECORD_ID, A.STEP_SEQ, A.STEP_NAME, A.ACTION_TYPE, A.ACTION_ID, A.DESCRIPTION, A.WORKFLOW_RULE, A.FORM_SECTION_ID, A.PRIORITY, A.ALLOW_ASSIGN_DUE_DATE, A.ALLOW_STOP, A.ON_COMPLETE_ACK_METHOD, A.FILTER_BY, A.ON_NO_RESPONSE_FOR, A.ON_NO_RESPONSE_CALENDAR_ID, A.ON_NO_RESPONSE_AFTER, A.ON_NO_RESPONSE_ACTION, A.ON_NO_RESPONSE_NEXT_STEP_ID, A.ON_NO_RESPONSE_ACK_METHOD, A.RECORD_STATUS, A.UPDATE_COUNT, A.CREATOR_ID, A.CREATE_DATE, A.UPDATER_ID, A.UPDATE_DATE ");
        sqlStat.append(" FROM      WORKFLOW_STEP A, MTM_WF_STEP_WF_STEP B ");
        sqlStat.append(" WHERE     A.RECORD_STATUS = ? AND B.RECORD_STATUS = ? AND A.ID = B.PARENT_STEP_ID AND B.CHILD_STEP_ID = ? ");
        sqlStat.append(" ORDER BY A.STEP_SEQ ");
        
        preStat = dbConn.prepareStatement(sqlStat.toString(), ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
        this.setPrepareStatement(preStat, 1, GlobalConstant.RECORD_STATUS_ACTIVE);
        this.setPrepareStatement(preStat, 2, GlobalConstant.RECORD_STATUS_ACTIVE);
        this.setPrepareStatement(preStat, 3, childStepID);
        rs = preStat.executeQuery();
        
        while (rs.next()) {
          WorkflowStep tmpWorkflowStep = new WorkflowStep();
          tmpWorkflowStep.setID(getResultSetInteger(rs, "ID"));
          tmpWorkflowStep.setWorkflowRecordID(getResultSetInteger(rs, "WORKFLOW_RECORD_ID"));
          tmpWorkflowStep.setStepSeq(getResultSetInteger(rs, "STEP_SEQ"));
          tmpWorkflowStep.setStepName(getResultSetString(rs, "STEP_NAME"));
          tmpWorkflowStep.setActionType(getResultSetString(rs, "ACTION_TYPE"));
          tmpWorkflowStep.setActionID(getResultSetInteger(rs, "ACTION_ID"));
          tmpWorkflowStep.setDescription(getResultSetString(rs, "DESCRIPTION"));
          tmpWorkflowStep.setWorkflowRule(getResultSetString(rs, "WORKFLOW_RULE"));
          tmpWorkflowStep.setSectionID(getResultSetString(rs, "FORM_SECTION_ID"));
          tmpWorkflowStep.setPriority(getResultSetString(rs, "PRIORITY"));
          tmpWorkflowStep.setAllowAssignDueDate(getResultSetString(rs, "ALLOW_ASSIGN_DUE_DATE"));
          tmpWorkflowStep.setAllowStop(getResultSetString(rs, "ALLOW_STOP"));
          tmpWorkflowStep.setOnCompleteAckMethod(getResultSetString(rs, "ON_COMPLETE_ACK_METHOD"));
          tmpWorkflowStep.setOnNoResponseFor(getResultSetInteger(rs, "ON_NO_RESPONSE_FOR"));
          tmpWorkflowStep.setOnNoResponseCalendarID(getResultSetString(rs, "ON_NO_RESPONSE_CALENDAR_ID"));
          tmpWorkflowStep.setOnNoResponseAfter(getResultSetTimestamp(rs, "ON_NO_RESPONSE_AFTER"));
          tmpWorkflowStep.setOnNoResponseAction(getResultSetString(rs, "ON_NO_RESPONSE_ACTION"));
          tmpWorkflowStep.setOnNoResponseNextStepID(getResultSetInteger(rs, "ON_NO_RESPONSE_NEXT_STEP_ID"));
          tmpWorkflowStep.setOnNoResponseAckMethod(getResultSetString(rs, "ON_NO_RESPONSE_ACK_METHOD"));
          tmpWorkflowStep.setFilterBy(getResultSetInteger(rs, "FILTER_BY"));
          tmpWorkflowStep.setRecordStatus(getResultSetString(rs, "RECORD_STATUS"));
          tmpWorkflowStep.setUpdateCount(getResultSetInteger(rs, "UPDATE_COUNT"));
          tmpWorkflowStep.setCreatorID(getResultSetInteger(rs, "CREATOR_ID"));
          tmpWorkflowStep.setCreateDate(getResultSetTimestamp(rs, "CREATE_DATE"));
          tmpWorkflowStep.setUpdaterID(getResultSetInteger(rs, "UPDATER_ID"));
          tmpWorkflowStep.setUpdateDate(getResultSetTimestamp(rs, "UPDATE_DATE"));
          tmpWorkflowStep.setCreatorName(UserInfoFactory.getUserFullName(tmpWorkflowStep.getCreatorID()));
          tmpWorkflowStep.setUpdaterName(UserInfoFactory.getUserFullName(tmpWorkflowStep.getUpdaterID()));
          parentActionType.add(tmpWorkflowStep);
        }
        return(parentActionType);
      } catch (ApplicationException appEx) {
        throw appEx;
      } catch (SQLException sqle) {
        log.error(sqle, sqle);
        throw new ApplicationException(ErrorConstant.DB_GENERAL_ERROR, sqle, sqle.toString());
      } catch (Exception e) {
        log.error(e, e);
        throw new ApplicationException(ErrorConstant.DB_SELECT_ERROR, e);
      } finally {
        try { rs.close(); } catch (Exception ignore) {} finally { rs = null; }
        try { preStat.close(); } catch (Exception ignore) {} finally { preStat = null; }
      }
    }
  }
  
  /**
   * get step list by workflowRecordID, formRecordID and userRecordID
   * @param workflowRecordID
   * @param formRecordID
   * @param userRecordID
   * @return
   * @throws ApplicationException
   */
  public synchronized List getStepListByWorkflowRecordIDFormRecordIDUserRecordID(
      Integer workflowRecordID, Integer formRecordID, Integer userRecordID) throws ApplicationException {
    PreparedStatement preStat = null;
    ResultSet rs = null;
    StringBuffer sqlStat = new StringBuffer();
    List result = new ArrayList();
    
    String groupIDStr = "-1";
    String roleIDStr = "-1";
    
    //get user group ID string
    UserGroupDAObject userGroupDAO = new UserGroupDAObject(this.sessionContainer, this.dbConn);
    List listGroup = userGroupDAO.getListByUserRecordIDGroupType(userRecordID, UserGroup.GROUP_TYPE_PUBLIC);
    
    for (int i = 0; i < listGroup.size(); i++) {
      UserGroup temp = (UserGroup)listGroup.get(i);
      groupIDStr += "," + temp.getID().toString();
    }    
    
    //get user role ID string.
    UserRoleDAObject userRoleDAO = new UserRoleDAObject(this.sessionContainer, this.dbConn);
    List listRole = userRoleDAO.getListByUserRecordID(userRecordID);
       
    for (int i = 0; i < listRole.size(); i++) {
      UserRole temp = (UserRole)listRole.get(i);
      roleIDStr += "," + temp.getID().toString();
    }
    synchronized(dbConn) {
      try {
        sqlStat.append("SELECT DISTINCT A.ID, A.WORKFLOW_RECORD_ID, A.STEP_SEQ, A.STEP_NAME, A.ACTION_TYPE, A.ACTION_ID, A.DESCRIPTION, A.WORKFLOW_RULE, A.FORM_SECTION_ID, A.PRIORITY, A.ALLOW_ASSIGN_DUE_DATE, A.ALLOW_ASSIGN_PRIORITY, A.ALLOW_STOP, A.ON_COMPLETE_ACK_METHOD, A.ON_NO_RESPONSE_FOR, A.ON_NO_RESPONSE_AFTER, A.ON_NO_RESPONSE_ACTION, A.ON_NO_RESPONSE_NEXT_STEP_ID, A.ON_NO_RESPONSE_ACK_METHOD, A.FILTER_BY, A.RECORD_STATUS, A.UPDATE_COUNT, A.CREATOR_ID, A.CREATE_DATE, A.UPDATER_ID, A.UPDATE_DATE ");        
        sqlStat.append("FROM WORKFLOW_STEP A  ");       
        
        sqlStat.append("INNER JOIN MTM_WF_STEP_FORM_RECORD B ON B.WORKFLOW_STEP_ID = A.ID ");
        sqlStat.append("INNER JOIN MTM_WF_RECORD_OBJECT_LINK C ON C.WORKFLOW_RECORD_ID = A.WORKFLOW_RECORD_ID "); 
        sqlStat.append("            AND C.OBJECT_TYPE = ? AND C.WORKFLOW_CATEGORY_ID = ? "); //M, 10
        
        sqlStat.append("INNER JOIN MTM_WF_STEP_USER_ACTOR D ON D.WORKFLOW_STEP_ID = A.ID ");
        sqlStat.append("           AND (  (D.ACTOR_TYPE = ? AND ACTOR_ID = ? )  "); // U, userID
        sqlStat.append("               OR (D.ACTOR_TYPE = ? AND D.ACTOR_ID IN ( " + groupIDStr +" ) ) ");// G, group id
        sqlStat.append("               OR (D.ACTOR_TYPE = ? AND D.ACTOR_ID IN ( " + roleIDStr +" ) ) ");// R, role id
        sqlStat.append("               OR (D.ACTOR_TYPE = ? ) )");// Z
        
       
        sqlStat.append("WHERE C.OBJECT_ID = B.FORM_RECORD_ID ");
        
        sqlStat.append("AND A.RECORD_STATUS = ? "); //A
        
        sqlStat.append("AND A.WORKFLOW_RECORD_ID = ? ");//workflowRecordID
        sqlStat.append("AND B.FORM_RECORD_ID = ? ");// form record id
        
        
        preStat = dbConn.prepareStatement(sqlStat.toString(), ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
        
        this.setPrepareStatement(preStat, 1, GlobalConstant.OBJECT_TYPE_FORM);
        this.setPrepareStatement(preStat, 2, SystemWorkflowConstant.FMB_SUBMISSION_BY_FORM);
        
        this.setPrepareStatement(preStat, 3, MtmWorkflowStepUserActor.ACTOR_TYPE_USER);
        this.setPrepareStatement(preStat, 4, userRecordID);  
        this.setPrepareStatement(preStat, 5, MtmWorkflowStepUserActor.ACTOR_TYPE_GROUP);        
        this.setPrepareStatement(preStat, 6, MtmWorkflowStepUserActor.ACTOR_TYPE_ROLE);
        this.setPrepareStatement(preStat, 7, MtmWorkflowStepUserActor.ACTOR_TYPE_EVERYONE);
        
        this.setPrepareStatement(preStat, 8, GlobalConstant.RECORD_STATUS_ACTIVE);
        
        this.setPrepareStatement(preStat, 9, workflowRecordID);
        this.setPrepareStatement(preStat, 10, formRecordID);
       
        rs = preStat.executeQuery();
        while (rs.next()) {
          WorkflowStep tmpWorkflowStep = new WorkflowStep();
          tmpWorkflowStep.setID(getResultSetInteger(rs, "ID"));
          tmpWorkflowStep.setWorkflowRecordID(getResultSetInteger(rs, "WORKFLOW_RECORD_ID"));
          tmpWorkflowStep.setStepSeq(getResultSetInteger(rs, "STEP_SEQ"));
          tmpWorkflowStep.setStepName(getResultSetString(rs, "STEP_NAME"));
          tmpWorkflowStep.setActionType(getResultSetString(rs, "ACTION_TYPE"));
          tmpWorkflowStep.setActionID(getResultSetInteger(rs, "ACTION_ID"));
          tmpWorkflowStep.setDescription(getResultSetString(rs, "DESCRIPTION"));
          tmpWorkflowStep.setWorkflowRule(getResultSetString(rs, "WORKFLOW_RULE"));
          tmpWorkflowStep.setSectionID(getResultSetString(rs, "FORM_SECTION_ID"));
          tmpWorkflowStep.setPriority(getResultSetString(rs, "PRIORITY"));
          tmpWorkflowStep.setAllowAssignDueDate(getResultSetString(rs, "ALLOW_ASSIGN_DUE_DATE"));
          tmpWorkflowStep.setAllowAssignPriority(getResultSetString(rs, "ALLOW_ASSIGN_PRIORITY"));
          tmpWorkflowStep.setAllowAssignDueDate(getResultSetString(rs, "ALLOW_STOP"));
          tmpWorkflowStep.setOnCompleteAckMethod(getResultSetString(rs, "ON_COMPLETE_ACK_METHOD"));
          tmpWorkflowStep.setOnNoResponseFor(getResultSetInteger(rs, "ON_NO_RESPONSE_FOR"));
          tmpWorkflowStep.setOnNoResponseAfter(getResultSetTimestamp(rs, "ON_NO_RESPONSE_AFTER"));
          tmpWorkflowStep.setOnNoResponseAction(getResultSetString(rs, "ON_NO_RESPONSE_ACTION"));
          tmpWorkflowStep.setOnNoResponseNextStepID(getResultSetInteger(rs, "ON_NO_RESPONSE_NEXT_STEP_ID"));
          tmpWorkflowStep.setOnNoResponseAckMethod(getResultSetString(rs, "ON_NO_RESPONSE_ACK_METHOD"));
          tmpWorkflowStep.setFilterBy(getResultSetInteger(rs, "FILTER_BY"));
          tmpWorkflowStep.setRecordStatus(getResultSetString(rs, "RECORD_STATUS"));
          tmpWorkflowStep.setUpdateCount(getResultSetInteger(rs, "UPDATE_COUNT"));
          tmpWorkflowStep.setCreatorID(getResultSetInteger(rs, "CREATOR_ID"));
          tmpWorkflowStep.setCreateDate(getResultSetTimestamp(rs, "CREATE_DATE"));
          tmpWorkflowStep.setUpdaterID(getResultSetInteger(rs, "UPDATER_ID"));
          tmpWorkflowStep.setUpdateDate(getResultSetTimestamp(rs, "UPDATE_DATE"));
          tmpWorkflowStep.setCreatorName(UserInfoFactory.getUserFullName(tmpWorkflowStep.getCreatorID()));
          tmpWorkflowStep.setUpdaterName(UserInfoFactory.getUserFullName(tmpWorkflowStep.getUpdaterID()));
          result.add(tmpWorkflowStep);
        }

        return(result);
      } catch (ApplicationException appEx) {
        throw appEx;
      } catch (SQLException sqle) {
        log.error(sqle, sqle);
        throw new ApplicationException(ErrorConstant.DB_GENERAL_ERROR, sqle, sqle.toString());
      } catch (Exception e) {
        log.error(e, e);
        throw new ApplicationException(ErrorConstant.DB_SELECT_ERROR, e);
      } finally {
        try { rs.close(); } catch (Exception ignore) {} finally { rs = null; }
        try { preStat.close(); } catch (Exception ignore) {} finally { preStat = null; }
      }
    }
  }
  
  /**
   * getStepListSQLByWorkflowRecordIDFormRecordIDUserRecordID
   * get the subQuery of workflow step which band with the form gived
   * @param workflowRecordID
   * @param formRecordID
   * @param userRecordID
   * @return the subQuery of workflow step which band with the form gived
   *
   */
  public synchronized AbstractQuery getStepListSQLByWorkflowRecordIDFormRecordIDUserRecordID(
      Integer workflowRecordID, Integer formRecordID, Integer userRecordID){
    
    AbstractQuery subQuery = new SubQuery();
       
    //get user group ID string
    UserGroupDAObject userGroupDAO = new UserGroupDAObject(this.sessionContainer, this.dbConn);
    AbstractQuery listGroup = userGroupDAO.getListSQLByUserRecordIDGroupType(userRecordID, UserGroup.GROUP_TYPE_PUBLIC);
 
    
    //get user role ID string.
    UserRoleDAObject userRoleDAO = new UserRoleDAObject(this.sessionContainer, this.dbConn);
    AbstractQuery listRole = userRoleDAO.getListSQLByUserRecordID(userRecordID);  
 
    
        subQuery.append("SELECT DISTINCT A.ID  ");        
        subQuery.append("FROM WORKFLOW_STEP A  ");       
        
        subQuery.append("INNER JOIN MTM_WF_STEP_FORM_RECORD B ON B.WORKFLOW_STEP_ID = A.ID ");
        subQuery.append("INNER JOIN MTM_WF_RECORD_OBJECT_LINK C ON C.WORKFLOW_RECORD_ID = A.WORKFLOW_RECORD_ID "); 
        subQuery.append("            AND C.OBJECT_TYPE = ? AND C.WORKFLOW_CATEGORY_ID = ? "); //M, 10
        
        subQuery.append("INNER JOIN MTM_WF_STEP_USER_ACTOR D ON D.WORKFLOW_STEP_ID = A.ID ");
        subQuery.append("           AND (  (D.ACTOR_TYPE = ? AND ACTOR_ID = ? )  "); // U, userID
        subQuery.append("               OR (D.ACTOR_TYPE = ? AND D.ACTOR_ID IN ( ").subQuery(listGroup).append(" ) ) ");// G, group id
        subQuery.append("               OR (D.ACTOR_TYPE = ? AND D.ACTOR_ID IN ( ").subQuery(listRole).append(" ) ) ");// R, role id
        subQuery.append("               OR (D.ACTOR_TYPE = ? ) )");// Z
        
       
        subQuery.append("WHERE C.OBJECT_ID = B.FORM_RECORD_ID ");
        
        subQuery.append("AND A.RECORD_STATUS = ? "); //A
        
        subQuery.append("AND A.WORKFLOW_RECORD_ID = ? ");//workflowRecordID
        subQuery.append("AND B.FORM_RECORD_ID = ? ");// form record id
        
       int i=1; 
  
       subQuery.setParameter(i++,  GlobalConstant.OBJECT_TYPE_FORM);
        
       subQuery.setParameter(i++, SystemWorkflowConstant.FMB_SUBMISSION_BY_FORM);
        
       subQuery.setParameter(i++, MtmWorkflowStepUserActor.ACTOR_TYPE_USER); 
       
       subQuery.setParameter(i++, userRecordID); 
          
       subQuery.setParameter(i++, MtmWorkflowStepUserActor.ACTOR_TYPE_GROUP); 
      
       subQuery.setParameter(i++, MtmWorkflowStepUserActor.ACTOR_TYPE_ROLE); 
      
       subQuery.setParameter(i++, MtmWorkflowStepUserActor.ACTOR_TYPE_EVERYONE); 
        
       subQuery.setParameter(i++, GlobalConstant.RECORD_STATUS_ACTIVE); 
    
       subQuery.setParameter(i++, workflowRecordID); 
     
       subQuery.setParameter(i++, formRecordID);
       
       subQuery.pack();
       
       return subQuery;
     
  }
  /**
   * 
   * @param workflowProgressID
   * @return
   * @throws ApplicationException
   */
  public synchronized AbstractBaseObject getStepByWorkflowProgress(Integer workflowProgressID) throws ApplicationException {
    PreparedStatement preStat = null;
    ResultSet rs = null;
    StringBuffer sqlStat = new StringBuffer();
    
    synchronized(dbConn) {
      try {
        sqlStat.append("SELECT A.ID, A.WORKFLOW_RECORD_ID, A.STEP_SEQ, A.STEP_NAME, A.ACTION_TYPE, A.ACTION_ID, A.DESCRIPTION, A.WORKFLOW_RULE, A.FORM_SECTION_ID, A.PRIORITY, A.ALLOW_ASSIGN_DUE_DATE, A.ALLOW_ASSIGN_PRIORITY, A.ALLOW_STOP, A.ON_COMPLETE_ACK_METHOD, A.ON_NO_RESPONSE_FOR, A.ON_NO_RESPONSE_CALENDAR_ID, A.ON_NO_RESPONSE_AFTER, A.ON_NO_RESPONSE_ACTION, A.ON_NO_RESPONSE_NEXT_STEP_ID, A.ON_NO_RESPONSE_ACK_METHOD, A.FILTER_BY, A.RECORD_STATUS, A.UPDATE_COUNT, A.CREATOR_ID, A.CREATE_DATE, A.UPDATER_ID, A.UPDATE_DATE ");
        sqlStat.append("FROM WORKFLOW_STEP A,  WORKFLOW_PROGRESS WP ");
        sqlStat.append("WHERE WP.WORKFLOW_STEP_ID=A.ID AND A.RECORD_STATUS = WP.RECORD_STATUS ");
        sqlStat.append("AND WP.ID = ? AND A.RECORD_STATUS = ? ");
        preStat = dbConn.prepareStatement(sqlStat.toString(), ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
        this.setPrepareStatement(preStat, 1, workflowProgressID);       
        this.setPrepareStatement(preStat, 2, GlobalConstant.RECORD_STATUS_ACTIVE);
        rs = preStat.executeQuery();
        if (rs.next()) {
          WorkflowStep tmpWorkflowStep = new WorkflowStep();
          tmpWorkflowStep.setID(getResultSetInteger(rs, "ID"));
          tmpWorkflowStep.setWorkflowRecordID(getResultSetInteger(rs, "WORKFLOW_RECORD_ID"));
          tmpWorkflowStep.setStepSeq(getResultSetInteger(rs, "STEP_SEQ"));
          tmpWorkflowStep.setStepName(getResultSetString(rs, "STEP_NAME"));
          tmpWorkflowStep.setActionType(getResultSetString(rs, "ACTION_TYPE"));
          tmpWorkflowStep.setActionID(getResultSetInteger(rs, "ACTION_ID"));
          tmpWorkflowStep.setDescription(getResultSetString(rs, "DESCRIPTION"));
          tmpWorkflowStep.setWorkflowRule(getResultSetString(rs, "WORKFLOW_RULE"));
          tmpWorkflowStep.setSectionID(getResultSetString(rs, "FORM_SECTION_ID"));
          tmpWorkflowStep.setPriority(getResultSetString(rs, "PRIORITY"));
          tmpWorkflowStep.setAllowAssignDueDate(getResultSetString(rs, "ALLOW_ASSIGN_DUE_DATE"));
          tmpWorkflowStep.setAllowAssignPriority(getResultSetString(rs, "ALLOW_ASSIGN_PRIORITY"));
          tmpWorkflowStep.setAllowStop(getResultSetString(rs, "ALLOW_STOP"));
          tmpWorkflowStep.setOnCompleteAckMethod(getResultSetString(rs, "ON_COMPLETE_ACK_METHOD"));
          tmpWorkflowStep.setOnNoResponseFor(getResultSetInteger(rs, "ON_NO_RESPONSE_FOR"));
          tmpWorkflowStep.setOnNoResponseCalendarID(getResultSetString(rs, "ON_NO_RESPONSE_CALENDAR_ID"));
          tmpWorkflowStep.setOnNoResponseAfter(getResultSetTimestamp(rs, "ON_NO_RESPONSE_AFTER"));
          tmpWorkflowStep.setOnNoResponseAction(getResultSetString(rs, "ON_NO_RESPONSE_ACTION"));
          tmpWorkflowStep.setOnNoResponseNextStepID(getResultSetInteger(rs, "ON_NO_RESPONSE_NEXT_STEP_ID"));
          tmpWorkflowStep.setOnNoResponseAckMethod(getResultSetString(rs, "ON_NO_RESPONSE_ACK_METHOD"));
          tmpWorkflowStep.setFilterBy(getResultSetInteger(rs, "FILTER_BY"));
          tmpWorkflowStep.setRecordStatus(getResultSetString(rs, "RECORD_STATUS"));
          tmpWorkflowStep.setUpdateCount(getResultSetInteger(rs, "UPDATE_COUNT"));
          tmpWorkflowStep.setCreatorID(getResultSetInteger(rs, "CREATOR_ID"));
          tmpWorkflowStep.setCreateDate(getResultSetTimestamp(rs, "CREATE_DATE"));
          tmpWorkflowStep.setUpdaterID(getResultSetInteger(rs, "UPDATER_ID"));
          tmpWorkflowStep.setUpdateDate(getResultSetTimestamp(rs, "UPDATE_DATE"));
          tmpWorkflowStep.setCreatorName(UserInfoFactory.getUserFullName(tmpWorkflowStep.getCreatorID()));
          tmpWorkflowStep.setUpdaterName(UserInfoFactory.getUserFullName(tmpWorkflowStep.getUpdaterID()));
          
          return(tmpWorkflowStep);
        } else {
          return null;
        }
      } catch (ApplicationException appEx) {
        throw appEx;
      } catch (SQLException sqle) {
        log.error(sqle, sqle);
        throw new ApplicationException(ErrorConstant.DB_GENERAL_ERROR, sqle, sqle.toString());
      } catch (Exception e) {
        log.error(e, e);
        throw new ApplicationException(ErrorConstant.DB_SELECT_ERROR, e);
      } finally {
        try { rs.close(); } catch (Exception ignore) {} finally { rs = null; }
        try { preStat.close(); } catch (Exception ignore) {} finally { preStat = null; }
      }
    }
  }
  
  /**
   * get the filtered UserIDs List From the WorkFlow Step Filter
   * @param userIdsList
   * @param wstep
   * @param wprogress
   * @return 
   * @throws ApplicationException
   */
  public ArrayList getfilteredListUserIDsFromWFStep(ArrayList userIdsList, WorkflowStep wstep, WorkflowProgress wprogress) throws ApplicationException {
    ArrayList resultList = new ArrayList();

    if (Utility.isEmpty(userIdsList)) {
      return userIdsList;
    }

    Integer filterBy = wstep.getFilterBy();
    if ((filterBy == null) || (filterBy.intValue() == 0)) {
      return userIdsList;
    }

    WorkflowProgressDAObject wfprogressDAO = new WorkflowProgressDAObject(this.sessionContainer, this.dbConn);

    // if filter by group
    if (filterBy.intValue() == -1) {
      UserGroupDAObject userGroupDAO = new UserGroupDAObject(this.sessionContainer, this.dbConn);
      List submitterGroupList = userGroupDAO.getListByUserRecordIDGroupType(wfprogressDAO.getUserIDForWorkflowTrackSubmitter(wprogress.getTrackID()), UserGroup.GROUP_TYPE_PUBLIC);

      for (Iterator iter = submitterGroupList.iterator(); iter.hasNext();) {
        UserGroup submitterGroup = (UserGroup) iter.next();

        for (Iterator iterator = userIdsList.iterator(); iterator.hasNext();) {
          Integer userID = (Integer) iterator.next();
          List userGroupList = userGroupDAO.getListByUserRecordIDGroupType(userID, UserGroup.GROUP_TYPE_PUBLIC);

          for (Iterator iteratorUser = userGroupList.iterator(); iteratorUser.hasNext();) {
            UserGroup userGroup = (UserGroup) iteratorUser.next();
            if (submitterGroup.getID().intValue() == userGroup.getID().intValue()) {
              resultList.add(userID);
              break;
            }
          }
        }

      }
      // if filter by role
    } else if (filterBy.intValue() == -2) {
      UserRoleDAObject userRoleDAO = new UserRoleDAObject(this.sessionContainer, this.dbConn);
      List submitterRoleList = userRoleDAO.getListByUserRecordID(wfprogressDAO.getUserIDForWorkflowTrackSubmitter(wprogress.getTrackID()));

      for (Iterator iter = submitterRoleList.iterator(); iter.hasNext();) {
        UserRole submitterRole = (UserRole) iter.next();

        for (Iterator iterator = userIdsList.iterator(); iterator.hasNext();) {
          Integer userID = (Integer) iterator.next();
          List userRoleList = userRoleDAO.getListByUserRecordID(userID);

          for (Iterator iteratorUser = userRoleList.iterator(); iteratorUser.hasNext();) {
            UserRole userRole = (UserRole) iteratorUser.next();
            if (submitterRole.getID().intValue() == userRole.getID().intValue()) {
              resultList.add(userID);
              break;
            }

          }
        }
      }
      // if filter by level
    } else {
      StaffRecordDAObject staffDAO = new StaffRecordDAObject(this.sessionContainer, this.dbConn);
      StaffRecord submitter = wfprogressDAO.getStaffRecordForWorkflowTrackSubmitter(wprogress.getTrackID());
      try {
        String submitterLevelID = org.apache.commons.beanutils.BeanUtils.getProperty(submitter, "level" + filterBy + "ID");
        for (Iterator iter = userIdsList.iterator(); iter.hasNext();) {
          Integer userID = (Integer) iter.next();
          StaffRecord staff = (StaffRecord) staffDAO.getObjectByUserRecordID(userID);
          String currentUserLevelID = org.apache.commons.beanutils.BeanUtils.getProperty(staff, "level" + filterBy + "ID");
          if (submitterLevelID.equals(currentUserLevelID)) {
            resultList.add(userID);
          }
        }
      } catch (Exception e) {
        log.error("  getProperty Error ");
      }

    }
    return resultList;
  }
  /**
   * get step list by workflowRecordID, formRecordID and userRecordID
   * @param workflowRecordID
   * @param formRecordID
   * @param userRecordID
   * @return
   * @throws ApplicationException
   */
  public synchronized List getUserOwnerAllStepListByWorkflowRecordIDFormRecordIDUserRecordID(
      Integer workflowRecordID, Integer formRecordID, Integer userRecordID) throws ApplicationException {
    PreparedStatement preStat = null;
    ResultSet rs = null;
    StringBuffer sqlStat = new StringBuffer();
    List result = new ArrayList();
    
    String groupIDStr = "-1";
    String roleIDStr = "-1";
    
    //get user group ID string
    UserGroupDAObject userGroupDAO = new UserGroupDAObject(this.sessionContainer, this.dbConn);
    List listGroup = userGroupDAO.getListByUserRecordIDGroupType(userRecordID, UserGroup.GROUP_TYPE_PUBLIC);
    
    for (int i = 0; i < listGroup.size(); i++) {
      UserGroup temp = (UserGroup)listGroup.get(i);
      groupIDStr += "," + temp.getID().toString();
    }    
    
    //get user role ID string.
    UserRoleDAObject userRoleDAO = new UserRoleDAObject(this.sessionContainer, this.dbConn);
    List listRole = userRoleDAO.getListByUserRecordID(userRecordID);
       
    for (int i = 0; i < listRole.size(); i++) {
      UserRole temp = (UserRole)listRole.get(i);
      roleIDStr += "," + temp.getID().toString();
    }
    synchronized(dbConn) {
      try {
        sqlStat.append("SELECT DISTINCT A.ID, A.WORKFLOW_RECORD_ID, A.STEP_SEQ, A.STEP_NAME, A.ACTION_TYPE, A.ACTION_ID, A.DESCRIPTION, A.WORKFLOW_RULE, A.FORM_SECTION_ID, A.PRIORITY, A.ALLOW_ASSIGN_DUE_DATE, A.ALLOW_ASSIGN_PRIORITY, A.ALLOW_STOP, A.ON_COMPLETE_ACK_METHOD, A.ON_NO_RESPONSE_FOR, A.ON_NO_RESPONSE_AFTER, A.ON_NO_RESPONSE_ACTION, A.ON_NO_RESPONSE_NEXT_STEP_ID, A.ON_NO_RESPONSE_ACK_METHOD, A.FILTER_BY, A.RECORD_STATUS, A.UPDATE_COUNT, A.CREATOR_ID, A.CREATE_DATE, A.UPDATER_ID, A.UPDATE_DATE ");        
        sqlStat.append("FROM WORKFLOW_STEP A,MTM_WF_STEP_USER_ACTOR D  ");       
        sqlStat.append("WHERE (  (D.ACTOR_TYPE = ? AND ACTOR_ID = ? )  "); // U, userID
        sqlStat.append("OR (D.ACTOR_TYPE = ? AND D.ACTOR_ID IN ( " + groupIDStr +" ) ) ");// G, group id
        sqlStat.append("OR (D.ACTOR_TYPE = ? AND D.ACTOR_ID IN ( " + roleIDStr +" ) ) ) ");// R, role id
        sqlStat.append("AND A.ID=D.WORKFLOW_STEP_ID AND A.WORKFLOW_RECORD_ID=? AND A.RECORD_STATUS=? AND D.RECORD_STATUS=? ");     
        log.debug("sqlStat>>>> "+sqlStat);
        preStat = dbConn.prepareStatement(sqlStat.toString(), ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);           
        this.setPrepareStatement(preStat, 1, MtmWorkflowStepUserActor.ACTOR_TYPE_USER);
        this.setPrepareStatement(preStat, 2, userRecordID);  
        this.setPrepareStatement(preStat, 3, MtmWorkflowStepUserActor.ACTOR_TYPE_GROUP);        
        this.setPrepareStatement(preStat, 4, MtmWorkflowStepUserActor.ACTOR_TYPE_ROLE);
        this.setPrepareStatement(preStat, 5, workflowRecordID);
        this.setPrepareStatement(preStat, 6, GlobalConstant.RECORD_STATUS_ACTIVE);
        this.setPrepareStatement(preStat, 7, GlobalConstant.RECORD_STATUS_ACTIVE);
        rs = preStat.executeQuery();
        while (rs.next()) {
          WorkflowStep tmpWorkflowStep = new WorkflowStep();
          tmpWorkflowStep.setID(getResultSetInteger(rs, "ID"));
          tmpWorkflowStep.setWorkflowRecordID(getResultSetInteger(rs, "WORKFLOW_RECORD_ID"));
          tmpWorkflowStep.setStepSeq(getResultSetInteger(rs, "STEP_SEQ"));
          tmpWorkflowStep.setStepName(getResultSetString(rs, "STEP_NAME"));
          tmpWorkflowStep.setActionType(getResultSetString(rs, "ACTION_TYPE"));
          tmpWorkflowStep.setActionID(getResultSetInteger(rs, "ACTION_ID"));
          tmpWorkflowStep.setDescription(getResultSetString(rs, "DESCRIPTION"));
          tmpWorkflowStep.setWorkflowRule(getResultSetString(rs, "WORKFLOW_RULE"));
          tmpWorkflowStep.setSectionID(getResultSetString(rs, "FORM_SECTION_ID"));
          tmpWorkflowStep.setPriority(getResultSetString(rs, "PRIORITY"));
          tmpWorkflowStep.setAllowAssignDueDate(getResultSetString(rs, "ALLOW_ASSIGN_DUE_DATE"));
          tmpWorkflowStep.setAllowAssignPriority(getResultSetString(rs, "ALLOW_ASSIGN_PRIORITY"));
          tmpWorkflowStep.setAllowAssignDueDate(getResultSetString(rs, "ALLOW_STOP"));
          tmpWorkflowStep.setOnCompleteAckMethod(getResultSetString(rs, "ON_COMPLETE_ACK_METHOD"));
          tmpWorkflowStep.setOnNoResponseFor(getResultSetInteger(rs, "ON_NO_RESPONSE_FOR"));
          tmpWorkflowStep.setOnNoResponseAfter(getResultSetTimestamp(rs, "ON_NO_RESPONSE_AFTER"));
          tmpWorkflowStep.setOnNoResponseAction(getResultSetString(rs, "ON_NO_RESPONSE_ACTION"));
          tmpWorkflowStep.setOnNoResponseNextStepID(getResultSetInteger(rs, "ON_NO_RESPONSE_NEXT_STEP_ID"));
          tmpWorkflowStep.setOnNoResponseAckMethod(getResultSetString(rs, "ON_NO_RESPONSE_ACK_METHOD"));
          tmpWorkflowStep.setFilterBy(getResultSetInteger(rs, "FILTER_BY"));
          tmpWorkflowStep.setRecordStatus(getResultSetString(rs, "RECORD_STATUS"));
          tmpWorkflowStep.setUpdateCount(getResultSetInteger(rs, "UPDATE_COUNT"));
          tmpWorkflowStep.setCreatorID(getResultSetInteger(rs, "CREATOR_ID"));
          tmpWorkflowStep.setCreateDate(getResultSetTimestamp(rs, "CREATE_DATE"));
          tmpWorkflowStep.setUpdaterID(getResultSetInteger(rs, "UPDATER_ID"));
          tmpWorkflowStep.setUpdateDate(getResultSetTimestamp(rs, "UPDATE_DATE"));
          tmpWorkflowStep.setCreatorName(UserInfoFactory.getUserFullName(tmpWorkflowStep.getCreatorID()));
          tmpWorkflowStep.setUpdaterName(UserInfoFactory.getUserFullName(tmpWorkflowStep.getUpdaterID()));
          result.add(tmpWorkflowStep);
        }

        return(result);
      } catch (ApplicationException appEx) {
        throw appEx;
      } catch (SQLException sqle) {
        log.error(sqle, sqle);
        throw new ApplicationException(ErrorConstant.DB_GENERAL_ERROR, sqle, sqle.toString());
      } catch (Exception e) {
        log.error(e, e);
        throw new ApplicationException(ErrorConstant.DB_SELECT_ERROR, e);
      } finally {
        try { rs.close(); } catch (Exception ignore) {} finally { rs = null; }
        try { preStat.close(); } catch (Exception ignore) {} finally { preStat = null; }
      }
    }
  }
  /**
   * Gets all the workflow steps of the given workflow record
   * 
   * @param workflowRecordID The workflow record ID
   * @return List of WorkflowStep objects
   * @throws ApplicationException
   * @see com.dcivision.workflow.bean.WorkflowStep
   * @see com.dcivision.workflow.bean.WorkflowRecord
   *///simon_add_S
  public synchronized List getListCurrentStepByStepID(Integer stepID) throws ApplicationException {
    PreparedStatement preStat = null;
    ResultSet rs = null;
    StringBuffer sqlStat = new StringBuffer();
    List result = new ArrayList();
    
    synchronized(dbConn) {
      try {
        sqlStat.append("SELECT    A.ID, A.STEP_NAME, A.RECORD_STATUS ");
        sqlStat.append("FROM      WORKFLOW_STEP A ");
        sqlStat.append("WHERE     A.RECORD_STATUS = ? AND A.ID = ? ");
        sqlStat.append("ORDER BY  A.ID ASC ");
        preStat = dbConn.prepareStatement(sqlStat.toString(), ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
        this.setPrepareStatement(preStat, 1, GlobalConstant.RECORD_STATUS_ACTIVE);
        this.setPrepareStatement(preStat, 2, stepID);
        rs = preStat.executeQuery();
        while (rs.next()) {
          WorkflowStep tmpWorkflowStep = new WorkflowStep();
          tmpWorkflowStep.setID(getResultSetInteger(rs, "ID"));
          tmpWorkflowStep.setStepName(getResultSetString(rs, "STEP_NAME"));
          tmpWorkflowStep.setRecordStatus(getResultSetString(rs, "RECORD_STATUS"));
          result.add(tmpWorkflowStep);
        }
        return(result);
      } catch (ApplicationException appEx) {
        throw appEx;
      } catch (SQLException sqle) {
        log.error(sqle, sqle);
        throw new ApplicationException(ErrorConstant.DB_GENERAL_ERROR, sqle, sqle.toString());
      } catch (Exception e) {
        log.error(e, e);
        throw new ApplicationException(ErrorConstant.DB_SELECT_ERROR, e);
      } finally {
        try { rs.close(); } catch (Exception ignore) {} finally { rs = null; }
        try { preStat.close(); } catch (Exception ignore) {} finally { preStat = null; }
      }
    }
  }
  //simon_add_E
  
  //king--add-start--
  public ArrayList getfilteredListProgressIDsFromWFStep(ArrayList userIdsList, WorkflowStep wstep, WorkflowProgress wprogress) throws ApplicationException {
	    ArrayList resultList = new ArrayList();

	    if (Utility.isEmpty(userIdsList)) {
	      return userIdsList; 
	    }

	    Integer filterBy = wstep.getFilterBy();
	    if ((filterBy == null) || (filterBy.intValue() == 0)) {
	      return userIdsList;
	    }

	    WorkflowProgressDAObject wfprogressDAO = new WorkflowProgressDAObject(this.sessionContainer, this.dbConn);

	    // if filter by group
	    if (filterBy.intValue() == -1) {
	      UserGroupDAObject userGroupDAO = new UserGroupDAObject(this.sessionContainer, this.dbConn);
	      List submitterGroupList = userGroupDAO.getListByUserRecordIDGroupType(wfprogressDAO.getUserIDForWorkflowTrackSubmitter(wprogress.getTrackID()), UserGroup.GROUP_TYPE_PUBLIC);

	      for (Iterator iter = submitterGroupList.iterator(); iter.hasNext();) {
	        UserGroup submitterGroup = (UserGroup) iter.next();

	        for (Iterator iterator = userIdsList.iterator(); iterator.hasNext();) {
	          Integer userID = (Integer) iterator.next();
	          List userGroupList = userGroupDAO.getListByUserRecordIDGroupType(userID, UserGroup.GROUP_TYPE_PUBLIC);

	          for (Iterator iteratorUser = userGroupList.iterator(); iteratorUser.hasNext();) {
	            UserGroup userGroup = (UserGroup) iteratorUser.next();
	            if (submitterGroup.getID().intValue() == userGroup.getID().intValue()) {
	              resultList.add(wprogress.getID());
	              break;
	            }
	          }
	        }

	      }
	      // if filter by role
	    } else if (filterBy.intValue() == -2) {
	      UserRoleDAObject userRoleDAO = new UserRoleDAObject(this.sessionContainer, this.dbConn);
	      List submitterRoleList = userRoleDAO.getListByUserRecordID(wfprogressDAO.getUserIDForWorkflowTrackSubmitter(wprogress.getTrackID()));

	      for (Iterator iter = submitterRoleList.iterator(); iter.hasNext();) {
	        UserRole submitterRole = (UserRole) iter.next();

	        for (Iterator iterator = userIdsList.iterator(); iterator.hasNext();) {
	          Integer userID = (Integer) iterator.next();
	          List userRoleList = userRoleDAO.getListByUserRecordID(userID);

	          for (Iterator iteratorUser = userRoleList.iterator(); iteratorUser.hasNext();) {
	            UserRole userRole = (UserRole) iteratorUser.next();
	            if (submitterRole.getID().intValue() == userRole.getID().intValue()) {
	              resultList.add(wprogress.getID());
	              break;
	            }

	          }
	        }
	      }
	      // if filter by level
	    } else {
	      StaffRecordDAObject staffDAO = new StaffRecordDAObject(this.sessionContainer, this.dbConn);
	      StaffRecord submitter = wfprogressDAO.getStaffRecordForWorkflowTrackSubmitter(wprogress.getTrackID());
	      try {
	        String submitterLevelID = org.apache.commons.beanutils.BeanUtils.getProperty(submitter, "level" + filterBy + "ID");
	        for (Iterator iter = userIdsList.iterator(); iter.hasNext();) {
	          Integer userID = (Integer) iter.next();
	          StaffRecord staff = (StaffRecord) staffDAO.getObjectByUserRecordID(userID);
	          String currentUserLevelID = org.apache.commons.beanutils.BeanUtils.getProperty(staff, "level" + filterBy + "ID");
	          if (submitterLevelID.equals(currentUserLevelID)) {
	            resultList.add(wprogress.getID());   
	          }
	        }
	      } catch (Exception e) {
	        log.error("  getProperty Error ");
	      }

	    }
	    return resultList;
	  }
  //king---add----end
  /**
   * 
   * @param workflowStepID
   * @param UserID
   * @return
   *///tc add
  public String getSubmissionRight(Integer workflowStepID,Integer UserID){
    Integer workflow_step_id=  workflowStepID;      
    Integer user_id= UserID;
    String right="R";
    right=checkSubmissionRight(workflow_step_id,user_id);
    if(right.equals("RW")){
      return right;     
    }
    //check by delegation       
    try {
    UserDelegationDAObject delegationDAO=new UserDelegationDAObject(sessionContainer,this.dbConn);
    List delegateList=delegationDAO.getListDelegatedToUser();
    Iterator delegateIt=delegateList.iterator();
    while(delegateIt.hasNext()){
      UserDelegation tmpUserDelegation=(UserDelegation)delegateIt.next();
      Integer sourceID=tmpUserDelegation.getSourceUserID();
      if(sourceID.equals(user_id)){
      right=checkSubmissionRight(workflow_step_id,sourceID);
      if(right.equals("RW")){
        return right;
      }
      }
    }
  } catch (ApplicationException e) {          
    log.error(e,e);
    return right;
  }
    return right;
  }
  
  /**
   * 
   * @param workflowStepID
   * @param userID
   * @return
   *///tc add
  private String checkSubmissionRight(Integer workflowStepID,Integer userID){ 
    String right="R";   
    Integer workflow_step_id=workflowStepID;        
    Integer user_id=userID;    
    try {     
      WorkflowStep tmpWorkflowStep =(WorkflowStep)this.getByID(workflow_step_id);            
      Integer wf_id=tmpWorkflowStep.getID();        
      MtmWorkflowStepUserActorDAObject wfUserActorDAO=new MtmWorkflowStepUserActorDAObject(this.sessionContainer,this.dbConn);      
      
      //check by user   
      List listUser=wfUserActorDAO.getListByWorkflowStepIDAndActorType(wf_id,GlobalConstant.SUBJECT_TYPE_USER);
      Iterator it=listUser.iterator();      
      while(it.hasNext()){
        MtmWorkflowStepUserActor tmpMtmWorkflowStepUserActor =(MtmWorkflowStepUserActor)it.next();
        Integer tmpUser_id=tmpMtmWorkflowStepUserActor.getActorID();       
        if(tmpUser_id.equals(user_id)){                  
            right="RW";
            return right; 
        }       
      } 
      //check by group
      MtmUserRecordUserGroupDAObject userGroupDAO=new MtmUserRecordUserGroupDAObject(sessionContainer,dbConn);
      List listGroup=userGroupDAO.getListByUserRecordID(user_id);
      Iterator itGroup=listGroup.iterator();
      while(itGroup.hasNext()){
        MtmUserRecordUserGroup tmpMtmUserRecordUserGroup=(MtmUserRecordUserGroup)itGroup.next();
        String groupID=String.valueOf(tmpMtmUserRecordUserGroup.getUserGroupID());
        List listWFByGroup=wfUserActorDAO.getListByWorkflowStepIDAndActorType(wf_id,GlobalConstant.SUBJECT_TYPE_GROUP);
        Iterator itWFByGroup=listWFByGroup.iterator();
        while(itWFByGroup.hasNext()){
          MtmWorkflowStepUserActor tmpMtmWorkflowStepUserActor =(MtmWorkflowStepUserActor)itWFByGroup.next();
          String tmpGroupID=String.valueOf(tmpMtmWorkflowStepUserActor.getActorID());
          if(tmpGroupID.equals(groupID)){
              right="RW";
              return right;
          }
        }
      }
      //check by role
      MtmUserRecordUserRoleDAObject userRoleDAO=new MtmUserRecordUserRoleDAObject(sessionContainer,dbConn);
      List listRole=userRoleDAO.getListByUserRecordID(user_id);
      List roleListID=new ArrayList();
      Iterator itRole=listRole.iterator();
      while(itRole.hasNext()){
        MtmUserRecordUserRole tmpMtmUserRecordUserRole = (MtmUserRecordUserRole)itRole.next();
        String roleID=String.valueOf(tmpMtmUserRecordUserRole.getUserRoleID());
        roleListID.add(Integer.valueOf(roleID));
        List listWFByRole=wfUserActorDAO.getListByWorkflowStepIDAndActorType(wf_id,GlobalConstant.SUBJECT_TYPE_ROLE);
        Iterator itWFByRole=listWFByRole.iterator();
        while(itWFByRole.hasNext()){
          MtmWorkflowStepUserActor tmpMtmWorkflowStepUserActor =(MtmWorkflowStepUserActor)itWFByRole.next();
          String tmpRoleID=String.valueOf(tmpMtmWorkflowStepUserActor.getActorID());
          if(tmpRoleID.equals(roleID)){         
              right="RW";
              return right;            
          }
        }
      }
      //check by group user role
      MtmUserGroupUserRoleDAObject userGroupRole=new MtmUserGroupUserRoleDAObject(sessionContainer,dbConn);     
      List groupUserRoleList=userGroupRole.getListByUserRoleAry(roleListID);      
      Iterator itGroupUserRole=groupUserRoleList.iterator();
      while(itGroupUserRole.hasNext()){
        MtmUserGroupUserRole tmpMtmUserGroupUserRole=(MtmUserGroupUserRole)itGroupUserRole.next();
        String groupID=String.valueOf(tmpMtmUserGroupUserRole.getUserRoleID());
        List listWFByGroup=wfUserActorDAO.getListByWorkflowStepIDAndActorType(wf_id,GlobalConstant.SUBJECT_TYPE_ROLE);
        Iterator itWFByGroup=listWFByGroup.iterator();
        while(itWFByGroup.hasNext()){
          MtmWorkflowStepUserActor tmpMtmWorkflowStepUserActor =(MtmWorkflowStepUserActor)itWFByGroup.next();
          String tmpGroupID=String.valueOf(tmpMtmWorkflowStepUserActor.getActorID());
          if(tmpGroupID.equals(groupID)){
              right="RW";
              return right;
            
          }
        }
      }
  }catch (ApplicationException e) {
      log.error(e,e);
    return right;
  } 
  return right;
}
 
  public List getSubmitterSuperVisorStepListByWorkflowRecordID(Integer workflowRecordID) throws ApplicationException{
    return getSubmitterSuperVisorStepListByWorkflowRecordIDWithPermissions(workflowRecordID,null);
  }
  public List getSubmitterSuperVisorStepListByWorkflowRecordIDWithPermissions(Integer workflowRecordID,String[] permissions) throws ApplicationException{
    PreparedStatement preStat = null;
    ResultSet rs = null;
    StringBuffer sqlStat = new StringBuffer();
    List result = new ArrayList();
    List parentActionType = new ArrayList();
    synchronized(dbConn) {
      try {
        sqlStat.append("SELECT  DISTINCT   A.ID, A.WORKFLOW_RECORD_ID, A.STEP_SEQ, A.STEP_NAME, A.ACTION_TYPE, A.ACTION_ID, A.DESCRIPTION, A.WORKFLOW_RULE, A.FORM_SECTION_ID, A.PRIORITY, A.ALLOW_ASSIGN_DUE_DATE, A.ALLOW_STOP, A.ON_COMPLETE_ACK_METHOD, A.FILTER_BY, A.ON_NO_RESPONSE_FOR, A.ON_NO_RESPONSE_CALENDAR_ID, A.ON_NO_RESPONSE_AFTER, A.ON_NO_RESPONSE_ACTION, A.ON_NO_RESPONSE_NEXT_STEP_ID, A.ON_NO_RESPONSE_ACK_METHOD, A.RECORD_STATUS, A.UPDATE_COUNT, A.CREATOR_ID, A.CREATE_DATE, A.UPDATER_ID, A.UPDATE_DATE ");
        sqlStat.append("FROM      WORKFLOW_STEP A, MTM_WF_STEP_USER_ACTOR B ");
        sqlStat.append("WHERE     A.RECORD_STATUS = ? AND B.RECORD_STATUS = ? AND A.ID = B.WORKFLOW_STEP_ID AND A.WORKFLOW_RECORD_ID = ? ");
        sqlStat.append(" AND B.ACTOR_TYPE = ? ");
        if(!Utility.isEmpty(permissions)){
          sqlStat.append(" AND (");
          for(int y=0;y<permissions.length;y++){
            if(y==0){
              sqlStat.append(" B.PERMISSION LIKE ? ");
            }else{
              sqlStat.append(" OR B.PERMISSION LIKE ? ");
            }
          }
          sqlStat.append(" )");
        }
        preStat = dbConn.prepareStatement(sqlStat.toString(), ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
        this.setPrepareStatement(preStat, 1, GlobalConstant.RECORD_STATUS_ACTIVE);
        this.setPrepareStatement(preStat, 2, GlobalConstant.RECORD_STATUS_ACTIVE);
        this.setPrepareStatement(preStat, 3, workflowRecordID);
        this.setPrepareStatement(preStat, 4, MtmWorkflowStepUserActor.ACTOR_TYPE_REPORT_TO);
        if(!Utility.isEmpty(permissions)){
          for(int i=0;i<permissions.length;i++){
            String keyWord = this.getFormattedKeyword(permissions[i],"LIKE");
            this.setPrepareStatement(preStat, i+5, keyWord);
          }
        }
        rs = preStat.executeQuery();
        
        while (rs.next()) {
          WorkflowStep tmpWorkflowStep = new WorkflowStep();
          tmpWorkflowStep.setID(getResultSetInteger(rs, "ID"));
          tmpWorkflowStep.setWorkflowRecordID(getResultSetInteger(rs, "WORKFLOW_RECORD_ID"));
          tmpWorkflowStep.setStepSeq(getResultSetInteger(rs, "STEP_SEQ"));
          tmpWorkflowStep.setStepName(getResultSetString(rs, "STEP_NAME"));
          tmpWorkflowStep.setActionType(getResultSetString(rs, "ACTION_TYPE"));
          tmpWorkflowStep.setActionID(getResultSetInteger(rs, "ACTION_ID"));
          tmpWorkflowStep.setDescription(getResultSetString(rs, "DESCRIPTION"));
          tmpWorkflowStep.setWorkflowRule(getResultSetString(rs, "WORKFLOW_RULE"));
          tmpWorkflowStep.setSectionID(getResultSetString(rs, "FORM_SECTION_ID"));
          tmpWorkflowStep.setPriority(getResultSetString(rs, "PRIORITY"));
          tmpWorkflowStep.setAllowAssignDueDate(getResultSetString(rs, "ALLOW_ASSIGN_DUE_DATE"));
          tmpWorkflowStep.setAllowStop(getResultSetString(rs, "ALLOW_STOP"));
          tmpWorkflowStep.setOnCompleteAckMethod(getResultSetString(rs, "ON_COMPLETE_ACK_METHOD"));
          tmpWorkflowStep.setOnNoResponseFor(getResultSetInteger(rs, "ON_NO_RESPONSE_FOR"));
          tmpWorkflowStep.setOnNoResponseCalendarID(getResultSetString(rs, "ON_NO_RESPONSE_CALENDAR_ID"));
          tmpWorkflowStep.setOnNoResponseAfter(getResultSetTimestamp(rs, "ON_NO_RESPONSE_AFTER"));
          tmpWorkflowStep.setOnNoResponseAction(getResultSetString(rs, "ON_NO_RESPONSE_ACTION"));
          tmpWorkflowStep.setOnNoResponseNextStepID(getResultSetInteger(rs, "ON_NO_RESPONSE_NEXT_STEP_ID"));
          tmpWorkflowStep.setOnNoResponseAckMethod(getResultSetString(rs, "ON_NO_RESPONSE_ACK_METHOD"));
          tmpWorkflowStep.setFilterBy(getResultSetInteger(rs, "FILTER_BY"));
          tmpWorkflowStep.setRecordStatus(getResultSetString(rs, "RECORD_STATUS"));
          tmpWorkflowStep.setUpdateCount(getResultSetInteger(rs, "UPDATE_COUNT"));
          tmpWorkflowStep.setCreatorID(getResultSetInteger(rs, "CREATOR_ID"));
          tmpWorkflowStep.setCreateDate(getResultSetTimestamp(rs, "CREATE_DATE"));
          tmpWorkflowStep.setUpdaterID(getResultSetInteger(rs, "UPDATER_ID"));
          tmpWorkflowStep.setUpdateDate(getResultSetTimestamp(rs, "UPDATE_DATE"));
          tmpWorkflowStep.setCreatorName(UserInfoFactory.getUserFullName(tmpWorkflowStep.getCreatorID()));
          tmpWorkflowStep.setUpdaterName(UserInfoFactory.getUserFullName(tmpWorkflowStep.getUpdaterID()));
          parentActionType.add(tmpWorkflowStep);
        }
        return(parentActionType);
      } catch (ApplicationException appEx) {
        throw appEx;
      } catch (SQLException sqle) {
        log.error(sqle, sqle);
        throw new ApplicationException(ErrorConstant.DB_GENERAL_ERROR, sqle, sqle.toString());
      } catch (Exception e) {
        log.error(e, e);
        throw new ApplicationException(ErrorConstant.DB_SELECT_ERROR, e);
      } finally {
        try { rs.close(); } catch (Exception ignore) {} finally { rs = null; }
        try { preStat.close(); } catch (Exception ignore) {} finally { preStat = null; }
      }
    }
  }
  
  public List getTheParentStepListByChildStpIDs(Integer[] childStepIDs)throws ApplicationException{
    PreparedStatement preStat = null;
    ResultSet rs = null;
    StringBuffer sqlStat = new StringBuffer();
    List result = new ArrayList();
    
    synchronized(dbConn) {
      try {
        sqlStat.append("SELECT DISTINCT A.ID, A.WORKFLOW_RECORD_ID, A.STEP_SEQ, A.STEP_NAME, A.ACTION_TYPE, A.ACTION_ID, A.DESCRIPTION, A.WORKFLOW_RULE, A.FORM_SECTION_ID, A.PRIORITY, A.ALLOW_ASSIGN_DUE_DATE, A.ALLOW_ASSIGN_PRIORITY, A.ALLOW_STOP, A.ON_COMPLETE_ACK_METHOD, A.ON_NO_RESPONSE_FOR, A.ON_NO_RESPONSE_AFTER, A.ON_NO_RESPONSE_ACTION, A.ON_NO_RESPONSE_NEXT_STEP_ID, A.ON_NO_RESPONSE_ACK_METHOD, A.FILTER_BY, A.RECORD_STATUS, A.UPDATE_COUNT, A.CREATOR_ID, A.CREATE_DATE, A.UPDATER_ID, A.UPDATE_DATE ");
        sqlStat.append(" FROM WORKFLOW_STEP A ");
        sqlStat.append(" WHERE A.ID IN( ");
        sqlStat.append(" SELECT B0.PARENT_STEP_ID ");
        sqlStat.append(" FROM ");
        for(int i=0;i<childStepIDs.length;i++){
          if(i==0){
            sqlStat.append("MTM_WF_STEP_WF_STEP B"+i);
          }else{
            sqlStat.append(", MTM_WF_STEP_WF_STEP B"+i);
          }
        }
        sqlStat.append(" WHERE ");
        for(int j=0;j<childStepIDs.length;j++){
          if(j==0){
            sqlStat.append(" B0.PARENT_STEP_ID = B"+j+".PARENT_STEP_ID ");
          }else{
            sqlStat.append(" AND B0.PARENT_STEP_ID = B"+j+".PARENT_STEP_ID ");
          }
        }
        sqlStat.append(" AND ");
        for(int y=0;y<childStepIDs.length;y++){
          sqlStat.append(" B"+y+".CHILD_STEP_ID=? AND B"+y+".RECORD_STATUS='"+GlobalConstant.RECORD_STATUS_ACTIVE+"' AND ");
        }
        sqlStat.append(" 1=1)");
        preStat = dbConn.prepareStatement(sqlStat.toString(), ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
        for(int k=0;k<childStepIDs.length;k++){
          this.setPrepareStatement(preStat, k+1, childStepIDs[k]);
        }
        rs = preStat.executeQuery();
        while (rs.next()) {
          WorkflowStep tmpWorkflowStep = new WorkflowStep();
          tmpWorkflowStep.setID(getResultSetInteger(rs, "ID"));
          tmpWorkflowStep.setWorkflowRecordID(getResultSetInteger(rs, "WORKFLOW_RECORD_ID"));
          tmpWorkflowStep.setStepSeq(getResultSetInteger(rs, "STEP_SEQ"));
          tmpWorkflowStep.setStepName(getResultSetString(rs, "STEP_NAME"));
          tmpWorkflowStep.setActionType(getResultSetString(rs, "ACTION_TYPE"));
          tmpWorkflowStep.setActionID(getResultSetInteger(rs, "ACTION_ID"));
          tmpWorkflowStep.setDescription(getResultSetString(rs, "DESCRIPTION"));
          tmpWorkflowStep.setWorkflowRule(getResultSetString(rs, "WORKFLOW_RULE"));
          tmpWorkflowStep.setSectionID(getResultSetString(rs, "FORM_SECTION_ID"));
          tmpWorkflowStep.setPriority(getResultSetString(rs, "PRIORITY"));
          tmpWorkflowStep.setAllowAssignDueDate(getResultSetString(rs, "ALLOW_ASSIGN_DUE_DATE"));
          tmpWorkflowStep.setAllowAssignPriority(getResultSetString(rs, "ALLOW_ASSIGN_PRIORITY"));
          tmpWorkflowStep.setAllowAssignDueDate(getResultSetString(rs, "ALLOW_STOP"));
          tmpWorkflowStep.setOnCompleteAckMethod(getResultSetString(rs, "ON_COMPLETE_ACK_METHOD"));
          tmpWorkflowStep.setOnNoResponseFor(getResultSetInteger(rs, "ON_NO_RESPONSE_FOR"));
          tmpWorkflowStep.setOnNoResponseAfter(getResultSetTimestamp(rs, "ON_NO_RESPONSE_AFTER"));
          tmpWorkflowStep.setOnNoResponseAction(getResultSetString(rs, "ON_NO_RESPONSE_ACTION"));
          tmpWorkflowStep.setOnNoResponseNextStepID(getResultSetInteger(rs, "ON_NO_RESPONSE_NEXT_STEP_ID"));
          tmpWorkflowStep.setOnNoResponseAckMethod(getResultSetString(rs, "ON_NO_RESPONSE_ACK_METHOD"));
          tmpWorkflowStep.setFilterBy(getResultSetInteger(rs, "FILTER_BY"));
          tmpWorkflowStep.setRecordStatus(getResultSetString(rs, "RECORD_STATUS"));
          tmpWorkflowStep.setUpdateCount(getResultSetInteger(rs, "UPDATE_COUNT"));
          tmpWorkflowStep.setCreatorID(getResultSetInteger(rs, "CREATOR_ID"));
          tmpWorkflowStep.setCreateDate(getResultSetTimestamp(rs, "CREATE_DATE"));
          tmpWorkflowStep.setUpdaterID(getResultSetInteger(rs, "UPDATER_ID"));
          tmpWorkflowStep.setUpdateDate(getResultSetTimestamp(rs, "UPDATE_DATE"));
          result.add(tmpWorkflowStep);
        }
        return result;
      }catch (ApplicationException appEx) {
        throw appEx;
      } catch (SQLException sqle) {
        log.error(sqle, sqle);
        throw new ApplicationException(ErrorConstant.DB_GENERAL_ERROR, sqle, sqle.toString());
      } catch (Exception e) {
        log.error(e, e);
        throw new ApplicationException(ErrorConstant.DB_SELECT_ERROR, e);
      } finally {
        try { rs.close(); } catch (Exception ignore) {} finally { rs = null; }
        try { preStat.close(); } catch (Exception ignore) {} finally { preStat = null; }
      }
    }
  }
}
